Page 1 of 1
Left Join with Subquery
Posted: Thu Mar 24, 2011 6:35 pm
by wasir
I am using subquery in left join to get data from both tables. Obviously I am doing something wrong that I am not getting value of "table2.mobile". Plz help to sort out the issue here...
Code: Select all
SELECT t1.id,t1.startDate,t1.endDate,t2.mobile
FROM table1 t1
LEFT JOIN (SELECT id,mobile FROM table2 ORDER BY dateAdded DESC LIMIT 1) as t2
ON t1.Id = t2.Id
ORDER BY t1.startDate DESC
Re: Left Join with Subquery
Posted: Fri Mar 25, 2011 1:57 am
by Weirdan
Does 'SELECT id,mobile FROM table2 ORDER BY dateAdded DESC LIMIT 1' return meaningful results?
Re: Left Join with Subquery
Posted: Fri Mar 25, 2011 2:07 am
by wasir
Yes. That one on itself is fine.
Re: Left Join with Subquery
Posted: Fri Mar 25, 2011 3:13 am
by Weirdan
what you're trying to do? The query how you wrote it would select all rows from table1 and add some information from table 2 to a single row of result (that row matching the join condition). I would imagine you wanted a correlated subquery in the select clause instead:
Code: Select all
SELECT t1.id,t1.startDate,t1.endDate,(select mobile FROM table2 t2 where t1.id=t2.id ORDER BY dateAdded DESC LIMIT 1) as mobile
FROM table1 t1
ORDER BY t1.startDate DESC
Re: Left Join with Subquery
Posted: Wed Mar 30, 2011 3:57 pm
by wasir
That makes clear sense now. Thanks for your help Weirdan.