outer join? - SQL Server Forums

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

SQL Server Development (2000)

outer join?

Author

Topic

jodyford
Starting Member

4 Posts

Posted - 2002-11-18 : 17:24:32

I have the following three tables.

tbl_sites(uid, name)
1, home
2, work

tbl_categories(uid, name)
1, cat
2, dog

pivot(site_uid, cat_uid)
1,1
1,2
2,1

I need to pass the value of the site (1, or 2) and get all categories and a boolean.

example
1 returns:
1 cat
1 Dog

2 returns:
1 cat
0 dog

I need the "dog" value to return a zero if site 2 has no subscribed to it.

I think this involves an outer join but I'm stuck.

thanks for the help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-18 : 17:56:54

Actually, a cross-join could work here. Get all combos of sites and users. Then, from there, do a LEFT OUTER JOIN to the table that combines sites and users (you called it the "Pivot" table). The boolean value (1 or 0) you return is based on whether or not there is a match in the outer table.

SELECT A.site_uid, CASE WHEN B.site_uid is NULL THEN 0 ELSE 1 END as BooleanVal, Cats_Name
FROM
(SELECT sites.uid as site_uid, cats.uid as cats_uid, cats.name as Cats_Name
FROM tbl_Sites sites
CROSS JOIN
tbl_categories cats
) A
LEFT OUTER JOIN
pivot B
ON
A.site_uid = B.site_uid AND
A.cats_uid = B.cats_uid

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-18 : 18:01:04

select c.name, case when p.cat_uid is null then 0 else 1 end
from tbl_categories c
left outer join pivot p
on p.cat_uid = c.uid
where p.site_uid = @site_uid

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-18 : 18:08:46

nr -- I don't think yours will work, you made a common left outer join error. The WHERE clause turns your OUTER JOIN into an INNER JOIN; you should rarely use a WHERE on an OUTER JOIN'd table.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-18 : 19:27:08

oops - deleted the wrong one

select c.name, case when p.cat_uid is null then 0 else 1 end
from tbl_categories c
left outer join pivot p
on p.cat_uid = c.uid
and p.site_uid = @site_uid

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jodyford
Starting Member

4 Posts

Posted - 2002-11-18 : 22:35:53

thats exactly what I needed. Thank you. Thank you all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources