CODEWITHSUNDEEP
mysqlsql
neilmarion
neilmarion

Reputation: 2382

SQL Sorting with 3 Joined Tables

I have these three tables

people
============
id, name

,

surveys
============
id, org_id

and

answer_sheets
============
id, person_id, survey_id, answer, date_answered

Now, what I wanted to do is to sort people in such in order basing it on the latest answer_sheets.date_answered (we can derive that one surveys AND people row can have many answer_sheets) given the surveys.org_id

Say for example we have the tables

people  
============
id  name  
1   Person1  
2   Person2  
3   Person3  
4   Person4  
5   Person5  

surveys
============
id  org_id  
1   1  
2   1
3   2
4   2


answer_sheets  
=============
id  person_id   survey_id   answer  date_answered  
1   1           1           string  JUN 13  
2   2           1           string  JUN 15  
3   3           2           string  JUN 17  
4   2           2           string  JUN 18  
5   1           2           string  JUN 19  
6   3           3           string  JUN 20  
7   2           3           string  JUN 25  
8   4           3           string  JUN 27  
9   4           4           string  JUN 27

and I wanted to order people in ASC order based on a people row's latest answer_sheets.date_answered in surveys having org_id = 1

the output would be

=============
id  name      last_date_answered
4   Person4   NIL
5   Person5   NIL
3   Person3   JUN 17
2   Person2   JUN 18
1   Person1   JUN 19

You can observe that people with ids 4 and 5 does not have an answer_sheet in surveys with org_id = 1 and yet they should be included in the list. Question: What must be the appropriate SQL query for this? Thanks.

Upvotes: 0

Views: 91

Answers (3)

Ghost
Ghost

Reputation: 2226

This query is still valid, however other answers are better. Fiddle is still valid though

http://sqlfiddle.com/#!2/3aac2/1/0

Here's about the best I could do for this time of night. Yes it's ugly (very, very ugly), but it gives the desired output. I really dislike that I had to use a union and an in, but it is late at night so I'm probably missing something. On that note, if this /is/ homework, you probably want to stay far far away from this solution. If it's not, there's a fiddle at the bottom to help other people help you

SELECT people.id,
   people.name,
   MAX(date_answered) AS date_answered
FROM people
LEFT OUTER JOIN answer_sheets ON answer_sheets.person_id = people.id
INNER JOIN surveys ON surveys.id = answer_sheets.survey_id
GROUP BY people.id,
     surveys.org_id HAVING surveys.org_id = 1
UNION
SELECT ALL people.id,
       people.name,
       NULL AS date_answered
FROM people
WHERE people.id NOT IN
    (SELECT person_id
     FROM answer_sheets
     INNER JOIN surveys ON answer_sheets.survey_id = surveys.id
     WHERE surveys.org_id = 1)
ORDER BY date_answered

Use one of the below solutions, left joining on a subquery is better. Fiddle is still valid though

If anyone wants to improve upon this, feel free to use this fiddle: http://sqlfiddle.com/#!2/3aac2/1/0

Upvotes: 1

neilmarion
neilmarion

Reputation: 2382

SELECT
  p.id, p.name, MAX(a.date_answered)
FROM
  people p
LEFT JOIN
  (SELECT * FROM 
    answer_sheets a
  INNER JOIN
    surveys S ON a.survey_id = s.id
  WHERE S.orgid = 1) a
ON
  p.id = a.personID
GROUP BY
  p.id, p.name
ORDER BY MAX(date_answered) ASC

This is the most optimized answer. Credits to my friend Sheila Ruth Fausto.

Upvotes: 0

Olivier Coilland
Olivier Coilland

Reputation: 3096

This query is simpler:

SELECT
    p.id,
    p.name,
    t.last_date_answered
FROM people p
LEFT JOIN (
    SELECT
        a.person_id,
        MAX(a.date_answered) as last_date_answered
    FROM answer_sheets a
    INNER JOIN surveys s ON a.survey_id = s.id
    WHERE s.org_id = 1
    GROUP BY a.person_id) t ON p.id = t.person_id
ORDER BY t.last_date_answered;

The UNION is a powerful too but here the LEFT JOIN is less verbose.

Upvotes: 1

Related Questions