'Mixing two tables together

I have two tables

db2:

id, db1id, text, count_db1

db1:

id, text

db2 above is created by

SELECT *, COUNT(`db1id`) AS count_db1 FROM `db2` GROUP BY `db1id` ORDER BY count_db1 DESC

so the last column is added and the whole output is sorted descendingly by count_db1.

db1ids are ids of db1. I want to

SELECT * FROM db1

ordered by the value of count_db1 in db1. If id of db1 is not existant in db2 as db1id it should be added to the end of the list(ie assigned a value of 0 for count_db1).

example:

db2:
id, db1id, text, count_db1
1,4,hello,5
2,4,hello,5
3,4,ho,5
5,4,yeah,5
6,4,no,5
4,3,no,1

db1:
id, text
3, yeahright
4, whatever

So in db2 db1id 4 occurs 5 times, db1id 3 occurs 1 time. So order the entries of db1 such that id 4 comes before id 3. Result should be:

  1. whatever
  2. yeahright
mysql


Solution 1:[1]

A simple LEFT JOIN with a COUNT should do what you want;

SELECT db1.*
FROM db1
LEFT JOIN db2
  ON db1.id=db2.db1id
GROUP BY db1.id, db1.text
ORDER BY COUNT(db2.id) DESC

An SQLfiddle to test with.

Solution 2:[2]

LEFT JOIN is preferred on this case because you still want the record on db1 to be shown on the list even if the id doesn't exist on db2.db1id.

SELECT  a.text
FROM    db1 a
        LEFT JOIN db2 b
            ON a.id = b.db1id
GROUP   BY a.id, a.text
ORDER   BY COUNT(b.db1id) DESC

To further gain more knowledge about joins, kindly visit the link below:

Solution 3:[3]

i did not test this. but maybe it works for you:

SELECT db1.text FROM db1 INNER JOIN `db2` ON db2.db1id = db1.id 
GROUP BY `db1id` 
ORDER BY (COUNT(`db1id`)) DESC

Edit: Since you want to see the "empty" records as well you should use LEFT JOIN as pointed out by the other posters.

SELECT db1.text FROM db1 LEFT JOIN `db2` ON db2.db1id = db1.id 
GROUP BY db1.id, db1.text 
ORDER BY (COUNT(`db1id`)) DESC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Joachim Isaksson
Solution 2 John Woo
Solution 3

Related Questions