How do I get a count of items in one column that match items in another column?

Question

Assume I have two data tables and a linking table as such:

A         B         A_B_Link          
-----     -----     -----
ID        ID        A_ID
Name      Name      B_ID

2 Questions:

1. I would like to write a query so that I have all of A's columns and a count of how many B's are linked to A, what is the best way to do this?

2. Is there a way to have a query return a row with all of the columns from A and a column containing all of linked names from B (maybe separated by some delimiter?)

Note that the query must return distinct rows from A, so a simple left outer join is not going to work here...I'm guessing I'll need nested select statements?

Answer 1

For #1

SELECT A.*, 
(SELECT COUNT(*) FROM A_B_Link WHERE A_B_Link.A_ID = AOuter.A_ID)
FROM A as AOuter

Answer 2

For your first question:

SELECT A.ID, A.Name, COUNT(ab.B_ID) AS bcount
FROM A LEFT JOIN A_B_Link ab ON (ab.A_ID = A.ID)
GROUP BY A.ID, A.Name;

This outputs one row per row of A, with the count of matching B's. Note that you must list all columns of A in the GROUP BY statement; there's no way to use a wildcard here.

An alternate solution is to use a correlated subquery, as @Ray Booysen shows:

SELECT A.*, 
  (SELECT COUNT(*) FROM A_B_Link 
   WHERE A_B_Link.A_ID = A.A_ID) AS bcount
FROM A;

This works, but correlated subqueries aren't very good for performance.

For your second question, you need something like MySQL's GROUP_CONCAT() aggregate function. In MySQL, you can get a comma-separated list of B.Name per row of A like this:

SELECT A.*, GROUP_CONCAT(B.Name) AS bname_list
FROM A 
  LEFT OUTER JOIN A_B_Link ab ON (A.ID = ab.A_ID)
  LEFT OUTER JOIN B ON (ab.B_ID = B.ID)
GROUP BY A.ID;

There's no easy equivalent in Microsoft SQL Server. Check here for another question on SO about this: "Simulating group_concat MySQL function in MS SQL Server 2005?"

Or Google for 'microsoft SQL server "group_concat"' for a variety of other solutions.

Answer 3

SELECT A.*, COUNT(B_ID)
FROM A
LEFT JOIN A_B_Link ab ON ab.A_ID=A.ID