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

Read the rest of this entry »