Finding Matches but including all

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)

Finding Matches but including all

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-06 : 08:21:47

MikeC writes "This sounds like a simple question, but I can't find the answer anywhere. I have two tables 1) a list of people, and 2) a list of selections for each person - selecting others from the original list of people. I need a query (or view) to show the entire list of people, but have a field indicating if that a person in the list was selected by a chosen person. I have used a 'right outer join' to show all people from the list, but when I select the criteria for the chosen person, it will show only those that match.

table1
id
firstname
lastname

table2
id_chosen
id_selected

example data
table1
1, george washington
2, john adams
3, thomas jefferson

table2
1, 1
1, 2
2, 1
2, 3

In this example, George Washington has selected himself and John Adams. John Adams has selected Washington and Jefferson. In need a query to produce this when George Washington is view his list:

1, George Washington, Selected
2, John Adams, Selected
3, Thomas Jefferson, Not Selected

Thanks"

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-06 : 08:32:22

select t1.firstname, t1.lastname, case when t2.id_selected is null then 'Not ' else '' end + 'Selected'
from table1 t1
left outer join table2 t2
on t2.id_selected = t1.id
and t2.id_chosen = @userid

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources