CODEWITHSUNDEEP
mysql
lummycoder
lummycoder

Reputation: 608

Join tables with specific order

I've got two tables, for example: Teacher and Pupil and table LastViewedPupil with fields who watched him and when (teacherId & pupilId). So I want to return the list of Pupils that was ordered by last viewed date, but there are not all pupils inside LastViewedPupil, but last few for example, I want to show after that ordered by date all left records no matter in wich order, how can I do that?

I can do without last part like 

select * from Pupil as p, (
    select * from LastViewedPupil lvp where lvp.teacherId = 5 ORDER BY lastViewDate
) as lvp where lvp.pupilId = p.pupilId;

Or should I add corresponding records in LastViewDatePupil for all pupils or need to Join table itself (sounds awkward)?

Upvotes: 0

Views: 48

Answers (1)

JCalcines
JCalcines

Reputation: 1286

You should try this one:

SELECT p.*
LEFT JOIN LastViewedPupil lvp ON p.id = lvp
WHERE lvp.teacher_id = 5
ORDER BY lvp.lastViewDate DESC

I'm not sure if that query puts NULL at the beginning or at the end. If that doesn't order the results properly, try this other. I used a CASE for reordering data

SELECT p.*, 
   CASE WHEN lvp.lvp.lastViewDate IS NULL THEN 1 ELSE 0, END AS notNullfirst FROM Pupil p
LEFT JOIN LastViewedPupil lvp ON p.id = lvp
WHERE lvp.teacher_id = 5
ORDER BY notNullfirst, lvp.lastViewDate DESC

Upvotes: 2

Related Questions