how to use count with joint talbe

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to use count with joint talbe

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-02-02 : 20:33:55

I have multiple table with relational fields, i tried to use below script but it's calculation wrong value on TableC's, Anyone help for advice.

SELECT a.ID,b.Name,
,count(b.Col2) as TableBCol2
,count(c.Col2) as TableCCol2
FROM TableA as a
INNER JOIN TableB as b ON a.ID=b.ID
left JOIN TableC as c ON b.ID=c.ID
group by a.ID,b.Name

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-02 : 20:37:02

Maybe because you are using LEFT JOIN? Define "wrong value". It's returning the correct value according to your query, just isn't what you had in mind. Give us some more information as to why it is wrong, and THEN we can help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-02-02 : 20:39:53

i need to use left join because i have more dada on TableB and only few data on TableC, have to count both table with diff column, they are relational tables.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-02-02 : 21:02:30

here are the table structure and data

Declare @TableA table
(ID INT,
Col2 varchar (255)
)
INSERT INTO @TableA
SELECT 1, 'aa'
INSERT INTO @TableA
SELECT 1, 'bb'
INSERT INTO @TableA
SELECT 2, 'cc'
INSERT INTO @TableA
SELECT 2, 'dd'

Declare @TableB table
(ID INT,
Col2 varchar (255)
)
INSERT INTO @TableB
SELECT 1, 'a22a'
INSERT INTO @TableB
SELECT 1, 'b33b'
INSERT INTO @Tableb
SELECT 1, 'c44c'
INSERT INTO @TableB
SELECT 1, 'd55d'

Declare @TableC table
(ID INT,
Col2 varchar (255)
)
INSERT INTO @TableC
SELECT 3, 'a22a'
INSERT INTO @TableC
SELECT 4, 'b33b'

SELECT a.ID,b.Col2
,count(b.Col2) as TableBCol2
,count(c.Col2) as TableCCol2
FROM @TableA as a
INNER JOIN @TableB as b ON a.ID=b.ID
left JOIN @TableC as c ON b.ID=c.ID
group by a.ID,b.Col2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-02 : 21:51:02

You haven't explained why you think it's an incorrect value. We can't read your mind here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-02-02 : 22:22:43

i counted by each table,
select count(col2) from TableA where id=1
the output count is 2

select count(col2) from TableB where id=1
the output is 3

select count(col2) from TableC where id=1
the output is 0

I am trying to join all these 3 tables,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources