rockinaway Posted September 16, 2011 Share Posted September 16, 2011 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? Quote Link to comment Share on other sites More sharing options...
jcbones Posted September 16, 2011 Share Posted September 16, 2011 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. Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted September 16, 2011 Share Posted September 16, 2011 you can reference the fields that are grabbed from your join clause in the ORDER BY and WHERE clause Quote Link to comment Share on other sites More sharing options...
rockinaway Posted September 16, 2011 Author Share Posted September 16, 2011 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.. Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted September 16, 2011 Share Posted September 16, 2011 post your query.. i assume you have the ON clause specified? Quote Link to comment Share on other sites More sharing options...
rockinaway Posted September 16, 2011 Author Share Posted September 16, 2011 I haven't added the left join yet.. $query = 'SELECT id, first_name, last_name FROM users WHERE email = "'.$email_sent.'"'; Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted September 16, 2011 Share Posted September 16, 2011 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 Quote Link to comment Share on other sites More sharing options...
rockinaway Posted September 16, 2011 Author Share Posted September 16, 2011 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?.. Quote Link to comment Share on other sites More sharing options...
rockinaway Posted September 16, 2011 Author Share Posted September 16, 2011 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.. Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted September 16, 2011 Share Posted September 16, 2011 yes, the way you have it set up now..it will post the newest row.. Quote Link to comment Share on other sites More sharing options...
rockinaway Posted September 16, 2011 Author Share Posted September 16, 2011 Okay that's perfect, glad I got it, thanks Quote Link to comment Share on other sites More sharing options...
jcbones Posted September 16, 2011 Share Posted September 16, 2011 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted September 16, 2011 Share Posted September 16, 2011 Yes, INNER JOIN. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.