problems joining multiple tables

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)

problems joining multiple tables

Author

Topic

inversions
Starting Member

5 Posts

Posted - 2007-12-04 : 08:56:18

Hello,

I was wondering if anyone could provide some help with a query im trying to get working.

I have 3 tables which are as follows:


Table ID_Downloads:

ID			NumDownloads		Date

AA12345678		20			01/02/2007
AA12345678		20			02/03/2007
AB12385845		50			12/07/2007
BC84757383		66			05/07/2007
CC91828374		25			08/08/2007
BC84757383		30			15/06/2007
EE84725754		45			25/05/2006


Table ID_List:

ID			Title

AA12345678		fax 
AB12385845		picture
BC84757383		calendar
CC91828374		letter
CC98374834		invoice
DD89427472		memo

(ID is primary key in this table)


Table ID_Scores:

ID			Score

AA12345678		4 
AB12385845		0
BC84757383		3
CC91828374		5
CC98374834		0
AA12345678		3

i have a query which is as follows:


select ID_Scores.ID,ID_List.Title,sum(NumDownloads) as Downloads from ID_Downloads with(NOLOCK) 
left outer join ID_List on ID_Downloads.ID = ID_List.ID
where year(Date) = 2007
group by ID_Downloads.ID,Title
order by Downloads desc

That produces the output:


ID			Title			Downloads

BC84757383		calander		96
AB12385845		picture			50
AA12345678		fax			40
CC91828374		letter			25

basically i wish to join table ID_Scores with these as well to produce an output like


ID			Title			Downloads	Score


BC84757383		calander		96		3
AB12385845		picture			50		0
AA12345678		fax			40		7
CC91828374		letter			25		5

i have tried adding: left outer join ID_Scores on ID_Downloads.ID = ID_Score.ID
after the first join but with no success. I've also tried right joins and unions,
however I probably don’t have enough experience to realise where I’m going wrong.

(the db is very badly designed, unfortunately the data for each table feeds in from different sources hence all the need for joining etc)

I’m just picking up SQL so any advice or help you could give would be most welcome.

many thanks,
-Inversions

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-12-04 : 09:13:44

like this...


declare @ID_Downloads table (ID varchar(30),NumDownloads int,Date datetime)
insert into @ID_Downloads select 'AA12345678',20,	'01/02/2007' union all
select 'AA12345678',20,	'20070302' union all
select 'AB12385845',50,	'20070712' union all
select 'BC84757383',66,	'20070705' union all
select 'CC91828374',25,	'20070808' union all
select 'BC84757383',30,	'20070615' union all
select 'EE84725754',45,	'20060525'


declare @ID_List table (ID varchar(30),	Title varchar(30))
insert into @ID_List select 'AA12345678','fax' union all 
select 'AB12385845','picture' union all
select 'BC84757383'	,'calendar' union all
select 'CC91828374','letter' union all
select 'CC98374834','invoice' union all 
select 'DD89427472','memo'


declare @ID_Scores table (ID varchar(30),Score int)
insert into @ID_Scores select 'AA12345678',4 union all
select 'AB12385845',0 union all
select 'BC84757383',3 union all
select 'CC91828374',5 union all
select 'CC98374834',0 union all
select 'AA12345678',3


select t.id,title,downloads,sum(score) as scores
from
(
select d.id,l.Title,sum(NumDownloads) as Downloads
from @ID_Downloads d
left outer join @ID_List l on d.ID = l.ID
where year(Date) = 2007
group by d.ID,Title
) t
join @id_scores s on s.id = t.id
group by t.id,title,downloads

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 10:04:24

[code]SELECT l.ID,
l.Title,
d.Downloads,
s.Score
FROM @ID_List AS l
INNER JOIN (
SELECT ID,
SUM(NumDownloads) AS Downloads
FROM @ID_Downloads
WHERE Date >= '20070101'
AND Date < '20080101'
GROUP BY ID
) AS d ON d.ID = l.ID
INNER JOIN (
SELECT ID,
SUM(Score) AS Score
FROM @ID_Scores
GROUP BY ID
) AS s ON s.ID = l.ID[/code]

E 12°55'05.25"
N 56°04'39.16"

inversions
Starting Member

5 Posts

Posted - 2007-12-06 : 11:21:19

That worked wonderfully thanks! my apologies for taking so long to reply, haven't had internet access.

i was just wondering is there any difference (in terms of efficiency) from using the year(date) = xxxx function and using date = 'yyyymmdd' with between?

many thanks again,
-Inversions

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 11:27:32

On a large table, the time difference is significant.
The YEAR(Col1) renders any present index over the datetime colum useless and you will get a index scan at best.
The other approach works very well with indexes, preferrably a clustered index.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources