Alan J. WilliamsOnline profile

Today I was writing a query to retrieve a number of records in a table which were associated to several different identifiers from a second table, which in turn were resolved from a third table.

I had a quick look on line to see if there were any quick examples I could start the basis off, but most examples were for just obvious queries or single count(*) and misrepresented. So I have an example of my solution below:

SELECT
	COUNT(1) as "Count",
	table2.name as "Name"
FROM table1
LEFT JOIN table2 on table2.id = table1.table2id
WHERE table1.table2id IN
(
	SELECT table2.id FROM table2 WHERE table2.field2='clause'
)
GROUP BY table2.name

This basically totals up the amount of records in table1 that match the table2.id (zero to many matches) individually. So instead of a standard

COUNT(*)
67

We get:

Count	Name
24	name1
43	name2

And so can see the total of each corresponding match there is.