Count from detail table

#1

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

#2

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

#3

On 20 feb, 18:17, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Count from detail table

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Count from detail table - 02-20-2008 , 10:57 AM

Re: Count from detail table - 02-20-2008 , 11:17 AM

Re: Count from detail table - 02-21-2008 , 02:41 AM