join the same table more than 3 times

[filmar]

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
left join Votes v1
ON l.number = v1.number and v1.ingroup = 1 and v1.checked =1
left join Votes v2
ON l.number = v2.number and v2.ingroup = 2 and v2.checked =1
left join Votes v3
ON l.number = v3.number and v3.ingroup = 3 and v3.checked =1
left join Votes v4
ON l.number = v4.number and v4.ingroup = 4 and v4.checked =1
left join Votes v5
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

[iburyak]

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.

[filmar]

iburyak thanks at all. It works!!! =)