CODEWITHSUNDEEP
mysql
khstex
khstex

Reputation: 21

MySQL: Order by when value is not in table

I'm working on a problem where I have two tables the first one sort of like this:

id | value
1    d
2    e
3    s
4    g

and another like this

value
d
e
e
s
s

and I need to order them according to the number of times they occur in the second table so e would be first since it occurs 2 times and ahead of s by alphabetical order and then d. So the end result should be something like this

id | value
2    e
3    s
1    d
4    g

Currently I have this which does almost exactly what I want

SELECT table1.id, ordering.count
FROM table1, 
    (SELECT value, COUNT(*) AS count 
     FROM table2 
     GROUP BY value 
     ORDER BY count) AS ordering
WHERE table1.value = ordering.value
ORDER BY ordering.count DESC, table1.value;

The only problem is because g does not appear in table2, the result is the result I want but without the last row with g since it does not appear in the ordering table. Is there a way I can get it to order it as if it were 0 even though it doesn't appear in the list or is any way to get around this using what I currently have?

Any pointers are appreciated.

Upvotes: 1

Views: 40

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133390

Why not a simply 

SELECT table1.id, table1.value
FROM table2 
INNER JOIN table2 on table1.value = table2.value
GROUP BY value 
ORDER BY table2.count(*) desc, table1.value desc

Upvotes: 1

DRapp
DRapp

Reputation: 48169

You will need a LEFT-JOIN to your summary table...

SELECT 
      table1.id, 
      table1.value,
      coalesce( ordering.count, 0 ) as FinalCount
   FROM 
      table1
         LEFT JOIN ( SELECT 
                           value, 
                           COUNT(*) AS count 
                        FROM 
                           table2 
                        GROUP BY 
                           value 
                        ORDER BY count) AS ordering
            ON table1.value = ordering.value
   ORDER BY 
      COALESCE( ordering.count, 0 ) DESC, 
      table1.value

The left join says give me all records on the left-side table (your table 1 as it appears first) regardless of a match in the right-side (your subquery). However, IF it finds a record in the subquery, grab its count. The COALESCE() says if the first value is null, then use the next -- in this case, 0 if no match found.

Upvotes: 0

Related Questions