473,241 Members | 1,612 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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 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

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

Similar topics

2
by: Richard J Lacroix | last post by:
I have two tables that I am trying to use to create a report by using a join. When the query is executed, the SUM on the second table is 4 times the expected amount. As a stand alone query on the...
3
by: mheydman | last post by:
I apologize if this has been asked before- I searched google but could not find a concrete answer. I recently inherited a database whose t-sql code is written in a format that I find difficult...
25
by: Mike MacSween | last post by:
Regular viewers may want to turn off now. This will be an orchestral management system. Musicians and other staff being booked/paid for jobs. A job may contain other jobs, e.g: World Tour...
6
by: davegb | last post by:
I'm trying to create a self-join table to show the relationship between employee and supervisor. In another thread, I was advised to create a SupervisorID in the employee table, a separate...
7
by: Chris | last post by:
I'm using ASP.NET and SQL Server and this might be an obviuos question for most, but if I have a table that contains several fields that I need to relate to just one field in another table, how do...
0
by: Vendell | last post by:
Join one of Canada's finest business men... Dear entrepreneur colleague: Here is a message from the founder.... Let me introduce myself. My name is Ariel Topf. I am 42 years old and I have...
33
by: Steve | last post by:
One of our clients recently upgraded their Office version to 2003. When they tried to run our program (written in Access 2000), they ended up with the wrong data. My coworker and I have tested this...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
12
by: info | last post by:
The following query works fine in access and does exactly what I want it to do however I get a syntax error when I port it over to SQL Server 2000. ------------- UPDATE OrdersProducts INNER...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.