Complex Query - counting / grouping...

[zigo86]

You have two tables with the following data: (ignore letter case – it’s not case sensitive)


Table1
-------
Col1 Col2
------- -------
alpha …
bravo …
charlie …
delta …


Table2
-------
Col1 Col2
------- -------
Alpha …
Alpha …
Alpha …
Bravo …
bravo …


Now, I need a query that does the following:

Selects each record in Table1, and for each record counts how many records there are in table2 with the same first column value.

If there aren’t any values in table2.col1 that match the values in table1.col1 it still has to produce the value from table1.col1, but say zero count.


So it has to come up with the following:


Value Count in table2
------- ---------------
alpha 3
bravo 2
charlie 0
delta 0


The problem is, if you put in ‘where table1.col1 = table2.col1’ then it doesn’t display the values from table1 that don’t have any corresponding values in table2.

In other words, it only produces results where values appear in both tables, so charlie and delta don’t appear.

I bet there's a quick solution, and would be most grateful if it could be brought to my attention :thumbsup:

zigo

[zigo86]

Damn, spaces got stripped out, tables don't quite line up, look a bit funny, but you get the idea.

[raf]

instead of using a generic query, specify the outer join yourself.

SELECT ... FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col1

[zigo86]

Legend! It worked. The final query is:

select table1.col1, count(table2.col1) from table1 left join table2 on table1.col1 = table2.col1 group by table2.col1

Thanks raf! :thumbsup:

[raf]

You're welcome. Glad you got it working.