Solved

Nested query gives subquery is not introduced with EXISTS error message

Posted on 2015-02-23
3
521 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

749 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