Help with rather complicated join

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

Transact-SQL (2000)

Help with rather complicated join

Author

Topic

WhiteEcho
Starting Member

3 Posts

Posted - 2006-04-21 : 15:47:45

I have a table with ads, a table with authors. Simple relationship on Author_ID field.

Some of these ads are tagged as "Specials".

Authors can have more than one ads.

What I need to do is return a "Has_Specials" fields when I pull the ads, so that when I list the ads I can a button linking to the special ads placed by the author.

I have thought about creating a temp table containing all the Author_IDs along with a COUNT(Ad_ID) WHERE Special = 1 and give it a BIT type that way it will be set to 0 or 1 when equal to 1 or more.

Then simply joining the Ads table with this temp tabl eon Ad_IDs, but for some reason I find this very clumsy.

Is there a possibility to do it with a CASE?

I tried to use a CASE but the Author_ID is not known at that point.

Thanks for the help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-21 : 15:59:40

Could you provide a data example to make your explanation clearer?

Tara Kizer
aka tduggan

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-21 : 16:02:50

[code]Create table #authors (Author_ID int, Authname varchar(100))

Create table #ads (Author_ID int, Special bit)

insert into #authors values(1,'ABC')
insert into #authors values(2,'CDE')
insert into #authors values(3,'EFG')
insert into #authors values(4,'GHI')
insert into #authors values(5,'IJK')
insert into #authors values(6,'KLM')

insert into #ads values(1,0)
insert into #ads values(2,1)
insert into #ads values(4,1)
insert into #ads values(5,0)
insert into #ads values(8,1)

Select u.Authname, 'Has Special'
from #authors u
inner join #ads a on u.Author_ID = a.Author_ID
Where a.Special = 1

drop table #authors
drop table #ads[/code]

Srinika

WhiteEcho
Starting Member

3 Posts

Posted - 2006-04-21 : 16:12:31

well, you are only listing authors with specials, that is not complicated.

What I am trying to do is list all the ads and, in a bit field, return a value set to 1 when this author has other ads that are flagged as special.

In this case:

Create table #ads (AD_ID int, Author_ID int, Special bit)

insert into #ads values(1,1,0)
insert into #ads values(2,1,1)
insert into #ads values(3,2,0)
insert into #ads values(4,2,0)
insert into #ads values(5,3,0)
insert into #ads values(6,3,0)
insert into #ads values(7,4,1)
insert into #ads values(8,4,0)

I am looking for a way to return:
Ad_ID;Author_ID;Has_Specials
1;1;1
2;1;1
3;2;0
4;2;0
5;3;0
6;3;0
7;4;1
8;4;1

Has_Specials is set to 1 when this ad or any other ad by the same author is flagged as specials.

So far the temp table solution works but I think it can be encapsulated into one query

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-21 : 17:57:41

You can try this, but it won't work if an author has more than one special value of 1:

SELECT
A.AD_ID,
A.Author_ID,
ISNULL(B.Special, 0) AS Has_Specials
FROM #ads A LEFT JOIN #ads B
ON A.Author_ID = B.Author_ID AND B.Special = 1
ORDER BY A.AD_ID, A.Author_ID

If that's the case, give this a try:

SELECT
A.AD_ID,
A.Author_ID,
B.MaxSpecial
FROM #ads A JOIN
(SELECT Author_ID, MAX(CONVERT(int, Special)) AS MaxSpecial FROM #ads GROUP BY Author_ID) AS B
ON A.Author_ID = B.Author_ID

WhiteEcho
Starting Member

3 Posts

Posted - 2006-04-21 : 18:11:36

Thanks, Nosepicker, looks like you found a winner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources