JOIN help

Anon

Is there a way I can do a conditional table join?

For example. Say I have three tables A, A1, and A2. If some condition based on the value of column X in table A is true, then I want to join A with A1 and if that condition is false I want to join A with A2

This kind of idea.....

SELECT * from A if(A.X < 10){ INNER JOIN A1 }else{ INNER JOIN A2 } USING(id);

Can I do this? Or do I have to use multiple queries?

tomhath

Should be able to get what you want with a UNION. Just but both sides of the "if" in the where clauses.

SELECT from A INNER JOIN A1
where A.id = A1.id
and a.X < 10
UNION
select from A inner join A2
where A.id = A2.id
and A.X >= 10

[deleted]

JOIN's basically relationships, and conditional relationships don't exist.

You can however do an OUTER join on both tables and select from either of the tables depending on what's in your 'base' table, something like (incomplete query):

SELECT a, CASE WHEN a<10 THEN table2.b ELSE table1.b END
FROM table1 LEFT OUTER JOIN table2 LEFT OUTER JOIN table2

But what exactly do you need this for?

A forum, a FAQ, what else do you need?