I have the following tables:
TableA (id, tableB_id, tableC_id)
TableB (id, expirationDate)
TableC (id, expirationDate)
I want to retrieve all the results from TableA ordered by tableB.expirationDate and tableC.expirationDate. How can I do this?
Add a comment
|
3 Answers
select ta.*
from TableA ta
inner join TableB tb on ta.tableB_id = tb.id
inner join TableC tc on ta.tableC_id = tc.id
order by tb.expirationDate, tc.expirationDate
Update:
If you are not getting all the records, then you'll need to use a left outer join:
select ta.*
from TableA ta
left outer join TableB tb on ta.tableB_id = tb.id
left outer join TableC tc on ta.tableC_id = tc.id
order by tb.expirationDate, tc.expirationDate
Comments
If the result set is empty with the other suggestions, are you sure the data in the tables is actually correctly correlated to each other?
Can you post some sample rows for each table?
Comments
Have you tried:
SELECT a.* FROM TableA a
INNER JOIN TableB b on b.id = a.tableB_id
INNER JOIN TableC c on c.id = a.tableC_id
ORDER BY b.expirationDate, c.expirationDatetableB_id
1 Comment
Justin Ethier
You can try using
LEFT OUTER JOIN to the other tables, as suggested by OrbMan. Otherwise please provide sample data for each table.