473,241 Members | 1,612 Online

# join the same table more than 3 times

23
I have two tables and need to recieve counts of each groups in columns. The 1st table is necessary to recieve exactly 4 rows even if there no one match condition id the 2nd table.
It have to count if checked is equal to 1.

CREATE TABLE [dbo].[IDS] (
[number] [int] NOT NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[stat] (
[stat] [int] IDENTITY (1, 1) NOT NULL ,
[checked] [tinyint] NULL ,
[Number] [int] NOT NULL ,
[ingroup] [int] NOT NULL ,
) ON [PRIMARY]
GO

insert into IDS values(1)
insert into IDS values(2)
insert into IDS values(3)
insert into IDS values(4)
GO

insert into stat values(1,1,1)
insert into stat values(1,3,2)
insert into stat values(0,1,1)
insert into stat values(1,1,1)
insert into stat values(1,2,3)
insert into stat values(1,2,1)
insert into stat values(1,1,1)
insert into stat values(1,4,4)
GO

I`ve tried this one but doesn`t work:

select l.number, count(v1.stat) as vc1, count(v2.stat) as vc2,count(v3.stat) as vc3,count(v4.stat) as vc4,count(v5.stat) as vc5
from IDS l
ON l.number = v1.number and v1.ingroup = 1 and v1.checked =1
ON l.number = v2.number and v2.ingroup = 2 and v2.checked =1
ON l.number = v3.number and v3.ingroup = 3 and v3.checked =1
ON l.number = v4.number and v4.ingroup = 4 and v4.checked =1
ON l.number = v5.number and v5.ingroup = 5 and v5.checked =1
group by l.number
order by l.number
GO

That is result which I have to recieve:
number vc1 vc2 vc3 vc4
1 4 0 0 0
2 1 0 1 0
3 0 1 0 0
4 0 0 0 1

Pls, help to solve this task. It gets me mad so much, thanks
Apr 1 '07 #1
2 5257
iburyak
1,017 Expert 512MB
1. In your query you use table name [votest] and insert into [stat]

2. Your query is correct I see discrepancy in desired result and actual result but check your insert statement for number one you desire following result

[PHP]1 4 0 0 0[/PHP]

But you have only 3 records to satisfy your condition. So query is correct and desired result not. Also desired result has one less column then the query.

[PHP]-------------------------------------insert into stat values(1,1,1)
insert into stat values(1,3,2)
insert into stat values(0,1,1)
-------------------------------------insert into stat values(1,1,1)
insert into stat values(1,2,3)
insert into stat values(1,2,1)
-------------------------------------insert into stat values(1,1,1)
insert into stat values(1,4,4)[/PHP]

4. Try my query it uses one join and passes tables only once your query does it 5 times which makes my query much faster faster. But result is the same as yours.

[PHP]select l.number,
sum(CASE WHEN ingroup = 1 and checked =1 then 1 else 0 end) vc1,
sum(CASE WHEN ingroup = 2 and checked =1 then 1 else 0 end) vc2,
sum(CASE WHEN ingroup = 3 and checked =1 then 1 else 0 end) vc3,
sum(CASE WHEN ingroup = 4 and checked =1 then 1 else 0 end) vc4,
sum(CASE WHEN ingroup = 5 and checked =1 then 1 else 0 end) vc5
FROM IDS l
left join stat v1 on l.number = v1.number
GROUP BY l.number
ORDER BY 1[/PHP]

Good Luck.
Apr 1 '07 #2
filmar
23
iburyak thanks at all. It works!!! =)
Apr 1 '07 #3