Display true or false if exists in other table

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

Transact-SQL (2008)

Display true or false if exists in other table

Author

Topic

loydall
Starting Member

33 Posts

Posted - 2011-04-06 : 05:13:07

Hi - lets say I have a standard user table - UserID, FirstName, LastName etc..

And then I have a "Conferences" table - ConferenceID, ComnferenceName etc..

And then I have a ConferenceRegistrations table where we record which users are registered for which conferences - so - ConferenceID, UserID

I want to pull back a report that shows me ALL users and whether or not they are registered for specific weeks (with true or false)

So the resulting table would look something like:

UserID | Conference1 | Conference2 | Conference3
--------------------------------------------------
1 | True | False | True
2 | False | True | False

etc..

But I can't figure out the SQL to do this.

Any idea?

Thanks

Sachin.Nand

2937 Posts

Posted - 2011-04-06 : 05:29:31

Is the no of Conference fixed or dynamic ?

PBUH

loydall
Starting Member

33 Posts

Posted - 2011-04-06 : 05:40:14

Well - they change so rarely that I don't mind updating the script if/when they do change.

So lets just say we know that we have Conference1, Conference2 and Conference3

Thanks

Sachin.Nand

2937 Posts

Posted - 2011-04-06 : 06:03:21

Something like this


declare @t table(id int,Conference varchar(20))
insert @t
select 1,'C1' union all
select 1,'C2' union all
select 2,'C1'union all
select 3,'C3'


select id,case when c1 IS not null then 1 else 0 end Conference1,
case when c2 IS not null then 1 else 0 end Conference2,
case when c3 IS not null then 1 else 0 end Conference3
 from(
select * from @t)u
pivot
(max(Conference) for Conference in([C1],[C2],[C3]))v

PBUH

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-06 : 16:26:47

[code]
select u.userId,
case
when exists(select * from userConference reg where reg.userId = u.userId and reg.conferenceId = <id of conference 1> ) then 'TRUE'
else 'FALSE'
end as Conference1,
...
from user u[/code]The query would also return row for users not attending any conference.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-07 : 03:03:54

Try this:


;WITH C AS (
SELECT UserID,
       COALESCE(MAX(CASE WHEN ConferenceID = 1 THEN 'True' END), 'False') AS v1,
       COALESCE(MAX(CASE WHEN ConferenceID = 2 THEN 'True' END), 'False') AS v2,
       COALESCE(MAX(CASE WHEN ConferenceID = 3 THEN 'True' END), 'False') AS v3
  FROM Conferences
 GROUP BY UserID
)
SELECT U.UserID,
       COALESCE(v1, 'False') AS Conference1,
       COALESCE(v2, 'False') AS Conference2,
       COALESCE(v3, 'False') AS Conference3
  FROM Users AS U
       LEFT OUTER JOIN C
                    ON U.UserID = C.userID;

______________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources