Left Join with Subquery

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wasir
Forum Commoner
Posts: 49
Joined: Sun Jul 08, 2007 11:28 pm

Left Join with Subquery

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Left Join with Subquery

Post by Weirdan »

Does 'SELECT id,mobile FROM table2 ORDER BY dateAdded DESC LIMIT 1' return meaningful results?
wasir
Forum Commoner
Posts: 49
Joined: Sun Jul 08, 2007 11:28 pm

Re: Left Join with Subquery

Post by wasir »

Yes. That one on itself is fine.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Left Join with Subquery

Post 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
wasir
Forum Commoner
Posts: 49
Joined: Sun Jul 08, 2007 11:28 pm

Re: Left Join with Subquery

Post by wasir »

That makes clear sense now. Thanks for your help Weirdan.
Post Reply