Hello! i have a mysql problem - i need to execute a query which selects field from one table and joins another table which is determined by a field value in table 1. for example, table1 contains fields ID(primary int) and someotherfield(varchar). i need to join table2 or table3 depending on the someotherfield value. if someotherfield contains 'a', it joins table 2, if it contains 'b', it joins table3.

i tried to do it something like this:

SELECT * FROM table1 JOIN table2 ON table1.ID=table2.ID WHERE (table1.someotherfield='a') JOIN table3 ON table1.ID=table3.ID WHERE (table1.someotherfield='b')

however, it does not works. i get an error. any ideas?

8 Years
Discussion Span
Last Post by drjohn
SELECT * FROM table1 JOIN table2 ON table1.ID=table2.ID JOIN table3 ON table1.ID=table3.ID WHERE (table1.someotherfield='b') and  (table1.someotherfield='a')


Your problem is you are really trying to do two different queries and get the results in one output. To do that you need to run the selects separately and then use UNION to merge the results. Something like:

SELECT * FROM table1 INNER JOIN table2 ON table1.ID = table2.ID WHERE table1.someotherfield='a'
SELECT * FROM table1 INNER JOIN table3 ON table1.ID = table3.ID WHERE table1.someotherfield='b'

see how that does.


rch1231's solution will work if table2 and table3 are exactly the same. So, if they are not, you need to make sure to change the output so they do match.


The real question is why have you designed a database where you need to do this, and what are you trying to achieve - ie show the table schemas, and what you are looking for in the output.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.