CODEWITHSUNDEEP
mysqlselectjoingroup-by
guri
guri

Reputation: 684

Using JOINs and group by together in single sql query

I'm not able to use JOINS and group-by together: I searched for this...but I didn't find the solution..Problem is as below:

I have two tables, first is main_table with fields eid, name, status

Second table is followups with fields fid, eid, last_date, remarks, and next_date

Second table is used to store followup details of clients (client details stored in main_table) and i want to get last followup record for each client with selected date period (from_date to to_date) and sort by next_date in descending order.

i used the below query but not working

SELECT * 
FROM main_table as MT 
JOIN followups as MF on MT.eid=MF.eid 
WHERE MT.status='open' AND MF.NDate<='2012-12-07' 
GROUP BY MF.eid 
ORDER BY MF.next_date DESC

thanks in advance...guys

Upvotes: 2

Views: 14734

Answers (3)

user2443001
user2443001

Reputation:

Try This 

SELECT * FROM main_table AS MT 
LEFT JOIN (SELECT * FROM (SELECT * FROM followups ORDER BY next_date DESC) AS A GROUP BY eid) AS MF ON MT.eid=MF.eid 
WHERE MT.status='open' 
AND MF.NDate<='2012-12-07';

Upvotes: 2

Taryn
Taryn

Reputation: 247880

You can try something like this:

select m.eid,
  m.name,
  m.status,
  f1.last_date,
  f1.remarks,
  f1.next_date
from maintable m
left join
(
  select max(last_date) maxLast, eid
  from followups
  where last_date between from_date and to_date
  group by eid
) f
  on m.eid = f.eid
left join followups f1
  on f.maxLast = f1.last_date
  and f.eid = f1.eid
where m.status='open'
  and f1.next_date<='2012-12-07'
order by f1.next_date desc

Upvotes: 2

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT * FROM main_table AS MT 
LEFT JOIN (SELECT * FROM (SELECT * FROM followups WHERE NDate<='2012-12-07' ORDER BY next_date DESC) AS A GROUP BY eid) AS MF ON MT.eid=MF.eid 
WHERE MT.status='open';

Upvotes: 1

Related Questions