Count from detail table

[Cacho]

Hi

I've two tables

TableA
id
name

TableB
id
tableA_id
desc
oper
As you can see tableA is a master and tableB is a detail table where
we can have many records for each related tableA record.

I need to get all records for tableA with a count on some oper of
tableB.

I suppose I can got it with a join or a subselect but I don't use SQL
often so I'm getting crazy with this stupid query...

Could somebody help ?

Thanks in advance

C

[Plamen Ratchev]

It is a good practice to name related columns with the same name across
tables.

Here is a query that will give you the counts for selected oper:

SELECT A.id, A.name, COUNT(B.oper) AS oper_cnt
FROM TableA AS A
LEFT OUTER JOIN TableB AS B
ON A.id = B.tablea_id
AND B.oper = 'a'
GROUP BY A.id, A.name

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Cacho]

On 20 feb, 18:17, "Plamen Ratchev" <[e-mail]:

It is a good practice to name related columns with the same name across
tables.

Here is a query that will give you the counts for selected oper:

SELECT A.id, A.name, COUNT(B.oper) AS oper_cnt
FROM TableA AS A
LEFT OUTER JOIN TableB AS B
* ON A.id = B.tablea_id
*AND B.oper = 'a'
GROUP BY A.id, A.name

HTH,

Plamen Ratchevhttp://www.SQLStudio.com

Thank you !

C