469,128 Members | 1,518 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

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
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
Apr 1 '07 #1
2 5107
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

25 posts views Thread by Mike MacSween | last post: by
6 posts views Thread by davegb | last post: by
7 posts views Thread by Chris | last post: by
12 posts views Thread by info | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.