Simple Join Question - 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

Transact-SQL (2000)

Simple Join Question

Author

Topic

dotnetmonkey
Starting Member

6 Posts

Posted - 2007-02-23 : 10:25:47

I am trying to create a query to return a result set for a web site. I am fairly new to sql so forgive me if this is a simple question.

I have 2 tables...

Items table which contains id, itemName, itemDate columns

and

ItemsRoles table which contains rid and id columns (id being a foreign key from Items)

I want to create a query which I can pass an rid to and get back a result set that includes all of the items from the items table and a column for rid which will either be the rid passed in or NULL if this item is not linked to that role

so the result set if a value of 2 is passed in should be something like...

id ItemName rid
1 item1 2
5 item2 NULL
6 item3 2

I believe this should be possible with an outer join as I want to return all rows from the items table regardless of whether they are in the ItemsRoles. I just cant seem to figure out the query which will give me the desired result.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 10:28:30

[code]
select i.id, i.itemname, r.rid
from items i left join itemsroles r
on i.id = r.id
and r.rid = @rid
[/code]

dotnetmonkey
Starting Member

6 Posts

Posted - 2007-02-23 : 10:35:34

Great, that was exactly what I needed.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources