Jump to content

table join problem


Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.