Suppose I have two tables A and B. Table A has a primary key, A.ID for instance, and each row in B contains a reference to some A.ID (there is a foreign key column in B). As a specific example:
ID | Name | Age 1 | Bob | 25 2 | Dan | 30 3 | Tina | 20 4 | Val | 36 5 | Joe | 51 6 | Mel | 22
AID | Color 1 | Blue 1 | Green 1 | Black 3 | Green 4 | Blue 2 | Red 5 | Green 3 | Red
Now Suppose I want to get the rows of A amended and ordered by the count of references each row has in B. In this example I would want
ID | Name | Age | Count 1 | Bob | 25 | 3 3 | Tina | 20 | 2 2 | Dan | 30 | 1 4 | Val | 36 | 1 5 | Joe | 51 | 1 6 | Mel | 22 | 0
One way I've basically managed to do this is the following query:
Select Count(*), A.ID, A.Name, A.Age, B.Color From B Left Join A On B.AID=A.ID Group By A.ID Order By Count(*) Desc, B.Color Desc
That will return:
Count | A.ID | A.Name | A.Age | B.Color 3 | 1 | Bob | 25 | Blue(not actually sure what color would go here) 2 | 3 | Tina | 20 | Green 1 | 2 | Dan | 30 | Red 1 | 4 | Val | 36 | Blue 1 | 5 | Joe | 51 | Green 1 | 6 | Mel | 22 | NULL
This works because I can guarantee that the null values will show up after the rows that do have a non-null color due to the order by. However, the counts here are not exactly what I'm looking for, I'd prefer to have Mel's row display a 0 for the count.
I'm sure there is a better way to perform this type of query, does anyone have any suggestions on how I could make this cleaner?