Left join confusion

[rockinaway]

I have a normal query for the database and I was to do a left join. However, the data in this left join needs to be ordered first and only the latest values take (i.e. only one value taken).

 

I'm confused if I can include an order by for the left join and a where clause with it as well.

 

Would it be better to just have another query?

[jcbones]

NO, you can only have one ORDER BY clause in a LEFT JOIN query.  You could maybe use a sub-query though, but we would need some database structure, data, and your desired results.

[AyKay47]

you can reference the fields that are grabbed from your join clause in the ORDER BY and WHERE clause

[rockinaway]

Could you give an example of this?.. because i'd have 2 where clauses: one for the main query and one for the left join..

[AyKay47]

post your query.. i assume you have the ON clause specified?

[rockinaway]

I haven't added the left join yet..

 

$query = 'SELECT id, first_name, last_name
					        FROM users
					        WHERE email = "'.$email_sent.'"';

[AyKay47]

http://dev.mysql.com/doc/refman/5.0/en/join.html

 

read it, make an attempt, if you have issues write back and we will help

[rockinaway]

So far:

 

SELECT m.mind, id, first_name, last_name 
					        FROM users
					        LEFT JOIN minds AS m ON m.u_id = id
					        ORDER BY m.time
					        WHERE email = "'.$email_sent.'"

 

Do I need to put users as u and then use that as well?.. 

[rockinaway]

Okay, I've worked on it, and it's having the desired effect currently, but I want to check the query is doing what I hope it is:

 

$query = 'SELECT t.mind, m.id, m.first_name, m.last_name
											FROM users AS m
											LEFT JOIN minds AS t ON t.u_id = m.id
					       					WHERE m.email = "'.$email_sent.'"
					       					ORDER BY t.time DESC LIMIT 1';

 

So I want to collect data from the users table for that one user. And then I want to get the information from the minds table. In the minds table, each user has several rows and so I want to order them in descending order by their time and then take the first one and add it to the rest of the user data..

 

 

[AyKay47]

yes, the way you have it set up now..it will post the newest row..

[rockinaway]

Okay that's perfect, glad I got it, thanks

[jcbones]

I believe you can just make that a JOIN instead of LEFT JOIN.  That way you will not get empty results from the user table.  Which could happen if an error occurs on INSERTion.

[fenway]

Yes, INNER JOIN.