Link to home
Start Free TrialLog in
Avatar of brgdotnet
brgdotnetFlag for United States of America

asked on

Nested query gives subquery is not introduced with EXISTS error message

Suppose I have two tables, People and Club.
How do I find all of the people in the People table who are NOT in the Club table? I came up with the following query, however it does not work:

select SSN,FirstName,LastName from People where
SSN NOT IN (
select c.SSN,c.FirstName,c.LastName from ClubMembers c INNER JOIN People p
on c.SSN = p.SSN)

When I run that query I get this error message :
Msg 116, Level 16, State 1, Line 16
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.




People

insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('9999993344','Terry','Cosheran')
insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('1111111124','Mira','Greyson')
insert INTO People (SSN,FirstName,LastName) VALUES ('3333393345','Jimmy','Johsnon')
insert INTO People (SSN,FirstName,LastName) VALUES ('8888993346','ricky','nisha')
insert INTO People (SSN,FirstName,LastName) VALUES ('7777993347','merry','smillo')
insert INTO People (SSN,FirstName,LastName) VALUES ('2229993348','narni','cochi')


Club

insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('9999993344','Terry','Cosheran')
insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('1111111124','Mira','Greyson')
SOLUTION
Avatar of Mark Bullock
Mark Bullock
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial