SQL Advanced technique

[cicovec-at-hotmail]

I found this working

SELECT table1.table1_id, count(table2.table2_id) as count
FROM table1, table2
WHERE table1.table1_id = table2.table1_id
GROUP by table1.table1_id
But this returns matching rows only.
I need to select ALL data from table1 with added (or joined) number of
matching rows in table2.

When there are no records matching table1_id in table2.table1_id nothing is
returned. If I add a condition like "OR 1=1" all rows from table1 are returned,
but the counted number of mathing rows is not right.

Please help. Thank you for your time
I'm using Microsoft SQL Server 2000

[cicovec-at-hotmail]

Solution found:

SELECT table1.table1_id, count(table2.table2_id) as count
FROM table1
LEFT JOIN table2 ON table1.table1_id = table2.table1_id
GROUP by table1.table1_id

For anyone in the future.

[cicovec-at-hotmail]

I've got one more question, I would like to add one more column to the result
set. It should also contain number of matching rows in the second table, but
only the ones, where table2.active eqals 1.

Could someone help me plase, I've already spent a lot of time figuring out
this query.

Thank you in advance.

[MikerRoo]

You can use a correlated subquery, like the attached...

-- MikeR



SELECT
T1.table1_id,
count (T2.table1_id) AS count, -- Avoid using reserved words as
column names!
(
SELECT
Count (T2b.table1_id)
FROM
table2 T2b
WHERE
T2b.table1_id = T1.table1_id
AND
T2b.active = 1
)
AS ActiveCount
FROM
table1 T1
LEFT JOIN
table2 T2 ON T1.table1_id = T2.table1_id
GROUP BY
T1.table1_id

[cicovec-at-hotmail]

Thank you MikerRoo, I found a solution, but yours is much much better.

SELECT table1.table1_id, COUNT(DISTINCT table2.table2_id) AS count_all,
COUNT(DISTINCT table2b.table2_id) AS count_active
FROM table1 LEFT JOIN table2 ON (table1.table1_id = table2.table1_id)
LEFT JOIN table2 AS table2b ON (table1.table1_id = table2b.table1_id AND
table2b.active = 1)
GROUP BY table1.table1_id