Retrieve rows where count of linked items greater than zero
Posted in MSSQL on May 30th, 2010 by phoenixdigitalSay you have two tables where one is linked to the other by it’s ID or CODE and you only want to retrieve a list of Table1 items which have linked Table2 items. You also want to get the count of Table2 items for each.
Table1
id(int)
title(varchar)
another_id(int)
Table2
id(int)
table1_id(int)
title(varchar)
date_added(date)
1 2 3 4 5 6 | SELECT t1.id, t1.title, count(t2.id) FROM Table1 t1 JOIN Table2 t2 ON t2.table1_id = t1.id WHERE t1.another_id = 32 GROUP BY t1.code, t1.title HAVING count(t2.id) > 0 |
Now say you want a list of Table1 with the most recent Table2 item ONLY
1 2 3 4 5 6 7 8 | SELECT t1.*, t2.* FROM Table1 t1 INNER JOIN Table2 t2 ON t2.table1_id = t1.id LEFT JOIN Table2 t2later ON t2.table1_id = t2later.table1_id AND t2later.date_added > t2.date_added WHERE t2later.table1_id is null |