Solved

Nested query gives subquery is not introduced with EXISTS error message

Posted on 2015-02-23
3
442 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now