chiprivers Posted November 16, 2011 Share Posted November 16, 2011 I am struggling with a table join where the comparitive table fields are in different formats; one int and one var. I know that this is sloppy database construction but unfortunately it is not my database and I do not have any control over the table construction. In my query I have a join as below: SELECT ... ... FROM tableA LEFT JOIN tableB ON tableA.varField = tableB.intField Unfortunately this is not running because the datatypes in the fields that I am joining on are not the same datatype. Is there a function I can use to change the var value to an int before comparing it, or vice versa? I think from googling that I should be using cast() or convert() but I cannot find a clear explanation of these functions that has enabled me to workout if and how I could use either of these functions. Any help would be very much appreciated. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted November 16, 2011 Share Posted November 16, 2011 Unfortunately this is not running How do you know it is not running? What error or wrong result do you get? There's nothing technically wrong with what you posted. If the varField is a numerical value it will be converted to a float data type for the comparison. Without sample data, the actual query, and any php code involved, we cannot really help with what is causing the problem. Quote Link to comment Share on other sites More sharing options...
chiprivers Posted November 16, 2011 Author Share Posted November 16, 2011 I'm not actually using PHP and MySQL for this query, I am using MS Access which is querying an Oracle database. I have posted here as I usually work with PHP and MySQL and as I understand it SQL is pretty much SQL which ever application I am using. I think the problem with the query is due to Access not liking the comparison between INT and VARCHAR so although it would probably normally work in SQL I need to try and work around the MS Access issue. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.