problem with JOIN

[Deey]

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?
thanks!

[jbisono]

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')

[rch1231]

Hello,

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'
UNION
SELECT * FROM table1 INNER JOIN table3 ON table1.ID = table3.ID WHERE table1.someotherfield='b'

see how that does.

[pritaeas]

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.

[drjohn]

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.