Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

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')
0
brgdotnet
Asked:
brgdotnet
3 Solutions
 
Mark BullockQA Engineer IIICommented:
Try this:
select SSN,FirstName,LastName 
from People p
where p.SSN NOT IN (
select c.SSN 
from ClubMembers c )

Open in new window


You can try out queries on these tables using SQL fiddle: http://sqlfiddle.com/#!6/e4481/1
0
 
OriNetworksCommented:
You can avoid the subquery with a join.

Select SSN, FirstName, LastName From
Person p left join Club c on p.SSN=c.SSN WHERE c.SSN is null

Open in new window


Also, i must mention that i hope you arent actually storing SSN in plaintext and multiple places as in this example. That is a very sensitive piece of information any may violate certain laws depending on your location.
0
 
PortletPaulCommented:
I also hope you are not storing names in multiple tables, that is not a good thing to do either.

the LEFT OUTER JOIN as used by OriNetworks should be the most efficient method for this question

Select p.SSN, p.FirstName, p.LastName
From People p
left join clubMembers c on p.SSN = c.SSN
WHERE c.SSN is null
;

another method is: NOT EXISTS

select P.SSN, P.FirstName, P.LastName
from People p
where NOT EXISTS (
        SELECT NULL
        FROM ClubMembers c
        WHERE p.SSN = C.SSN
         )

and then there is also NOT IN (...) as demonstrated by Mark Bullock above.

------
The specific reason for that error message is that you have too many columns in your original subquery
"Only one expression can be specified in the select list"

select SSN,FirstName,LastName from People where
SSN NOT IN (
        select c.SSN ,c.FirstName,c.LastName    -- << ONLY ONE expression here
        from ClubMembers c
        INNER JOIN People p on c.SSN = p.SSN  -- << this join is not required
        )

see: http://sqlfiddle.com/#!6/8bf00/1

details:
**MS SQL Server 2012 Schema Setup**:

        create table people(
      SSN varchar(20),FirstName varchar(20), LastName varchar(20)
    );
    
    CREATE CLUSTERED INDEX IX_People_SSN
        ON dbo.People (SSN); 
    
    
    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');
    
    create table clubMembers (
    SSN varchar(20), FirstName varchar(20), LastName varchar(20)
    );
    
    CREATE CLUSTERED INDEX IX_clubMembers_SSN
        ON dbo.clubMembers (SSN); 
    
    insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('9999993344','Terry','Cosheran');
    insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('1111111124','Mira','Greyson');
    insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('9999993344','Terry','Cosheran');
    insert INTO ClubMembers (SSN,FirstName,LastName) VALUES ('1111111124','Mira','Greyson');
    
    
    
    

**Query 1**:

    Select p.SSN, p.FirstName, p.LastName 
    From People p 
    left join clubMembers c on p.SSN = c.SSN 
    WHERE c.SSN is null
    

**[Results][2]**:
    
    |        SSN | FIRSTNAME | LASTNAME |
    |------------|-----------|----------|
    | 2229993348 |     narni |    cochi |
    | 3333393345 |     Jimmy |  Johsnon |
    | 7777993347 |     merry |   smillo |
    | 8888993346 |     ricky |    nisha |


**Query 2**:

    select P.SSN, P.FirstName, P.LastName 
    from People p
    where NOT EXISTS (
            SELECT NULL
            FROM ClubMembers c
            WHERE p.SSN = C.SSN
         )
    

**[Results][3]**:
    
    |        SSN | FIRSTNAME | LASTNAME |
    |------------|-----------|----------|
    | 2229993348 |     narni |    cochi |
    | 3333393345 |     Jimmy |  Johsnon |
    | 7777993347 |     merry |   smillo |
    | 8888993346 |     ricky |    nisha |


**Query 3**:

    select p.SSN, p.FirstName, p.LastName 
    from People P
    where p.SSN NOT IN (
            select c.SSN    -- << ONLY ONE expression here
            from ClubMembers c 
                            -- << this join is not required
            )
    

**[Results][4]**:
    
    |        SSN | FIRSTNAME | LASTNAME |
    |------------|-----------|----------|
    | 2229993348 |     narni |    cochi |
    | 3333393345 |     Jimmy |  Johsnon |
    | 7777993347 |     merry |   smillo |
    | 8888993346 |     ricky |    nisha |



  [1]: http://sqlfiddle.com/#!6/8bf00/1

  [2]: http://sqlfiddle.com/#!6/8bf00/1/0

  [3]: http://sqlfiddle.com/#!6/8bf00/1/1

  [4]: http://sqlfiddle.com/#!6/8bf00/1/2

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now