Get The Count And Details From Multiple Tables Using Join

ISSUE
You need to query data as well as a count from another table. You don’t want to use two queries to accomplish what one could do.

FIX
Use a right outer join for your data table to your count table.

EXAMPLE
I have two tables, one Hardware which has attributes of hw_type, hw_equip_name, hw_manufacturer, and hw_decal_num. I have another table, HardwareUpdate, which keeps a hw_decal_num (foriegn key to Hardware) and an update_id (foriegn key to Update) to keep track of updates done on hardware.

I want to get the details of all the hardware as well as how many updates were performed on that specific piece of hardware.

My query:

SELECT h.hw_decal_num, h.hw_type, h.hw_equip_name, COUNT(hwu.hwu_id) AS hw_update_count
FROM hardwareupdate hwu RIGHT OUTER JOIN hardware h ON hwu.hw_decal_num = h.hw_decal_num
GROUP BY h.hw_decal_num, h.hw_type, h.hw_equip_name
ORDER BY h.hw_equip_name

Also to note, all the attributes you are using from the table you are getting data from (in our example it’s Hardware) should be listed in the GROUP BY clause.