brgdotnet
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.LastNa me 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','Cos heran')
insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('1111111124','Mira','Grey son')
insert INTO People (SSN,FirstName,LastName) VALUES ('3333393345','Jimmy','Joh snon')
insert INTO People (SSN,FirstName,LastName) VALUES ('8888993346','ricky','nis ha')
insert INTO People (SSN,FirstName,LastName) VALUES ('7777993347','merry','smi llo')
insert INTO People (SSN,FirstName,LastName) VALUES ('2229993348','narni','coc hi')
Club
insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('9999993344','Terry','Cos heran')
insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('1111111124','Mira','Grey son')
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.LastNa
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','Cos
insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('1111111124','Mira','Grey
insert INTO People (SSN,FirstName,LastName) VALUES ('3333393345','Jimmy','Joh
insert INTO People (SSN,FirstName,LastName) VALUES ('8888993346','ricky','nis
insert INTO People (SSN,FirstName,LastName) VALUES ('7777993347','merry','smi
insert INTO People (SSN,FirstName,LastName) VALUES ('2229993348','narni','coc
Club
insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('9999993344','Terry','Cos
insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('1111111124','Mira','Grey
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.