MySQL Forums
Forum List  »  MySQL Query Browser

left join querry with filters and group
Posted by: Reginald Bohn
Date: March 13, 2011 06:15PM

Hello everybody!

Since weeks I am trying to figure out how to solve the following:

I have the two tables "staffs" and "interviews" and every staff member gets regularly interviews. I want to see ALL male staffs exactly one time. So basically a list of all male staffs on the left side of the table. To these I want to see the latest interview date corresponding notes - if the interview was done in 2011. If the interview was in 2010 or earlier it should not be shown. If there was more than one interview in 2011 I want the latest only.

This here works pretty well and is close to what I need:

$sql = "SELECT lastname, firstname, MAX(date) as maxdate, note FROM staffs LEFT JOIN interviews ON staffs.id = interviews.staffid WHERE staffs.sex='m' GROUP BY staffs.id ORDER BY lastname";

(What doesn't work here as well is that the interview note does not belong to the max date.)

Basically I need something like this, putting the date condition in brackets:

$sql = "SELECT lastname, firstname, MAX(date) as maxdate, note FROM staffs LEFT JOIN (SELECT * FROM interviews WHERE date>'2011-12-31') ON staffs.id = interviews.staffid WHERE staffs.sex='m' GROUP BY staffs.id ORDER BY lastname";

Unfortunately this doesn't work ...

Can somebody help me???

Thanks,
Reg

Options: ReplyQuote


Subject
Written By
Posted
left join querry with filters and group
March 13, 2011 06:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.