Solved

Nested query gives subquery is not introduced with EXISTS error message

Posted on 2015-02-23
3
506 Views
Last Modified: 2015-02-23
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
Comment
Question by:brgdotnet
3 Comments
 
LVL 13

Assisted Solution

by:Mark Bullock
Mark Bullock earned 147 total points
ID: 40627364
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
 
LVL 17

Assisted Solution

by:OriNetworks
OriNetworks earned 146 total points
ID: 40627370
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 207 total points
ID: 40627419
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question