I am trying to select data from three tables. my logic should go like this:
Basically select from either one of the two tables which is determined by a column in table A.
Select a.orderid ,
if (a.ordertable= b) then b.order_info
else
c.order_info
where
a.order_id = b.order_id or a.order_id = c.order_id
Any pointers?
Use CASE
SELECT a.orderid,
CASE
WHEN a.ordertable = b.? THEN b.order_info
ELSE c.order_info
END
FROM sparkles
WHERE a.order_id = b.order_id OR a.order_id = c.order_id
What comes to mind is two subqueries, union'ed together to get the results from each table:
select *
from ((select b.order_info
from b join
a
on a.order_id = b.order_id and
a.ordertable = 'b'
)
union all
(select c.order_info
from c join
a
on a.order_id = c.order_id and
c.ordertable = 'c'
)
) t
Assuming the row in table b or c may or may not exist, I think you need this:
select a.orderid,
case
when a.ordertable = 'b' then b.order_info
else c.order_info
end as order_info
from a
left
join b
on a.orderid = b.orderid
left
join c
on a.orderid = c.orderid
