Tricky SELECT script - 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

General SQL Server Forums

New to SQL Server Programming

Tricky SELECT script

Author

Topic

heed
Starting Member

3 Posts

Posted - 2009-06-25 : 05:48:19

Hey,

I'm having some trouble figuring out how to do a select script.

I have two relevant tables, Subscriptions(userid, catid) and Categories(id, name).

Now what i want, given a userid, is a table with (catid, name, and whether or not the given userid is in subscriptions with that catid)

That probably didnt make any sense, so I'll give you an example:

With the following tables,

Subscriptions
userid   catid
1        1
1        3
2        1

Categories
catid   name
1       cars
2       food
3       other

I want, given userid=1, this table

catid   name    subscribes
1       cars    1
2       food    0
3       other   1

Any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-25 : 05:54:34

Try

select catid,name,case when t2.catid is null then 0 else 1 end as subscribes from Categories as t1
left join Subscriptions as t2 on t1.catid=t2.catid

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 06:43:29

[code]SELECT c.CatID,
c.Name,
CASE
WHEN s.UserID IS NULL THEN 0
ELSE 1
END AS Subscribes
FROM dbo.Categories AS c
LEFT JOIN dbo.Subscriptions AS s ON s.CatID = c.CatID
AND s.UserID = @UserID[/code]

E 12°55'05.63"
N 56°04'39.26"

heed
Starting Member

3 Posts

Posted - 2009-06-25 : 07:15:09

Excellent, thanks a lot for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources