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]

Re: Count from detail table

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]

Re: Count from detail table

On 20 feb, 18:17, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

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.c om

Thank you !

C