MySQL if/else for tables

Question

Is there a way to if else tables in mysql?

Example:

select * from tableA A, if(tableA.find=1,TableBA B,TableBB B) where A.id = B.aid

Real Example:

tables are: location_order, order_contract, order_gm, order_gm_hh:

select * 
from   location order lo, order_contract oc, if(lo.hh=0,order_gm,order_gm_hh) as og 
where  lo.orderid = oc.orderid && oc.gmid = og.id

Answer 1

You could have to separate this out into two select statements. For example:

select * from tableA A
inner join TableBA B on A.id = B.aid
where A.find = 1

UNION 

select * from tableA A
inner join TableBB B on A.id = B.aid
where A.find <> 1

Answer 2

You can left outer join both tables, and then use the IF() function in the SELECT clause:

SELECT     a.*,
           IF(a.find = 1, b1.value, b2.value) b_value
FROM       tableA a
LEFT JOIN  tableBA b1 ON (b1.aid = a.id)
LEFT JOIN  tableBB b2 ON (b2.aid = a.id);

Test case:

CREATE TABLE tableA (id int, find int, value int);
CREATE TABLE tableBA (id int, aid int, value int);
CREATE TABLE tableBB (id int, aid int, value int);

INSERT INTO tableA VALUES (1, 1, 100);
INSERT INTO tableA VALUES (2, 0, 200);
INSERT INTO tableA VALUES (3, 1, 300);
INSERT INTO tableA VALUES (4, 0, 400);

INSERT INTO tableBA VALUES (1, 1, 10);
INSERT INTO tableBA VALUES (2, 3, 20);

INSERT INTO tableBB VALUES (1, 2, 30);
INSERT INTO tableBB VALUES (2, 4, 40);

Result:

+------+------+-------+---------+
| id   | find | value | b_value |
+------+------+-------+---------+
|    1 |    0 |   100 |      10 |
|    2 |    1 |   200 |      30 |
|    3 |    0 |   300 |      20 |
|    4 |    1 |   400 |      40 |
+------+------+-------+---------+
4 rows in set (0.00 sec)

Answer 3

Do an outer join on both tables and use a case statement in the select to pull the value from whichever table meets the Find=1 criteria using a case statement.

SELECT     Case A.Find 
              WHEN 1 THEN B1.SomeField 
              ELSE B2.SomeField 
           END as SomeField
FROM       tableA A
LEFT JOIN  tableBA b1 ON (b1.aid = A.id)
LEFT JOIN  tableBB b2 ON (b2.aid = A.id);