CODEWITHSUNDEEP
mysqlsql
dcangulo
dcangulo

Reputation: 2107

Order records if it is in the other table

I have a contents table.

+------------+------+----------+
| content_id | year | has_file |
+------------+------+----------+
| 1          | 2017 | true     |
| 2          | 2018 | true     |
| 3          | 2016 | true     |
| 4          | 2018 | true     |
| 5          | 2018 | false    |
+------------+------+----------+

Then I have the uploads table.

+----+------------+------------+
| id | content_id | filename   |
+----+------------+------------+
| 1  | 2          | sample.jpg |
+----+------------+------------+

I want to fetch the data according to the latest year then followed by if it has a file.

I can do it using the query SELECT * FROM contents ORDER BY year DESC, has_file DESC.

The problem is not all records with has_file set to true has actually recorded on the uploads table.

Is can exclude all records that do not exist on the other table by adding WHERE EXISTS (SELECT 1 FROM uploads WHERE uploads.content_id = contents.content_id).

But I don't want to exclude it, I just want it to appear last and the records with files appear first. Is this possible?

Expected result:

+------------+------+----------+
| content_id | year | has_file |
+------------+------+----------+
| 2          | 2018 | true     |
| 4          | 2018 | true     |
| 5          | 2018 | false    |
| 1          | 2017 | true     |
| 3          | 2016 | true     |
+------------+------+----------+

Upvotes: 1

Views: 105

Answers (2)

Arunraj
Arunraj

Reputation: 568

You can use this as well,

SELECT 
    id, year, has_file
FROM
    (SELECT 
        c.id,
            c.year,
            c.has_file,
            CASE
                WHEN u.id IS NOT NULL THEN 1
                ELSE 2
            END AS flag
    FROM
        contents c
    LEFT JOIN uploads u ON (c.id = u.content_id)) tmp
ORDER BY flag , year DESC;

Upvotes: 1

Nick
Nick

Reputation: 147166

Just add the EXISTS clause to the ORDER BY clause of your query:

SELECT * 
FROM contents 
ORDER BY year DESC, 
         has_file DESC, 
         EXISTS (SELECT 1 FROM uploads WHERE uploads.content_id = contents.content_id) DESC

Output:

content_id  year    has_file
2           2018    true
4           2018    true
5           2018    false
1           2017    true
3           2016    true

SQLFiddle Demo

Upvotes: 7

Related Questions