CODEWITHSUNDEEP
sqlexists
parkjohnston
parkjohnston

Reputation: 3

Exists SQL Statement

I am trying to find all year and caseseqnumbers in a table where the type is not appellant Rep 1. The error is coming from the fact that a year and caseseqnumber can have many rows in the table. This is what i have tried: 

Select caseseqnumber, year
from caseparticipants
where not exists (Select *
                  from caseparticipants
                  where participanttype = 'Appellant Rep 1')

Any help?!

Upvotes: 0

Views: 112

Answers (5)

fthiella
fthiella

Reputation: 49079

You could join caseparticipants with itself, using a LEFT JOIN. If the join doens't succeed, it means that caseseqnumber and year don't have a row with participanttype = 'Appellant Rep 1':

SELECT
  c1.caseseqnumber, 
  c1.year 
FROM 
  caseparticipants c1 LEFT JOIN caseparticipants c2
  ON c1.year=c2.year AND c1.caseseqnumber=c2.caseseqnumber
     AND c2.participanttype = 'Appellant Rep 1'
WHERE
  c2.year IS NULL

EDIT

To compare the number of distinct combinations of caseseqnumber, year, and the number of combinations that have a a type of 'Appellant Rep 1' you could use this SQL Server query:

SELECT
  COUNT(DISTINCT
    CAST(c1.caseseqnumber AS VARCHAR) + '-' + CAST(c1.year AS VARCHAR)),
  COUNT(DISTINCT
    CAST(c2.caseseqnumber AS VARCHAR) + '-' + CAST(c2.year AS VARCHAR))
FROM 
  caseparticipants c1 LEFT JOIN caseparticipants c2
  ON c1.year=c2.year AND c1.caseseqnumber=c2.caseseqnumber
     AND c2.participanttype = 'Appellant Rep 1'

Upvotes: 0

Juan Vilar
Juan Vilar

Reputation: 553

why do you need to do a nested search in there . Nested searches are needed only needed in case of checking things in more than one database Table.

stick to 

select caseqnumber,year from caseparticipants where paticipanttype <> 'Appellant Rep 1'

(<> is the sql clause for NOT EQUAL TO)

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30815

If you want all cases that have an appellant but not a rep:

Select caseseqnumber, year
from caseparticipants cp1
where not exists (Select null
                  from caseparticipants cp2
                  where cp2.participanttype = 'Appellant Rep 1'
                  and cp1.caseseqnumber = cp2.caseseqnumber
                  and cp1.year = cp2.year
)

Upvotes: 0

Himanshu
Himanshu

Reputation: 32602

Why don't you try to use WHERE condition directly in the main query instead of using a sub query?

SELECT caseseqnumber, year 
  FROM caseparticipants 
 WHERE participanttype != 'Appellant Rep 1'

Upvotes: 0

David Martin
David Martin

Reputation: 12248

Select 
    caseseqnumber, 
    year 
from 
    caseparticipants 
where 
    participanttype != 'Appellant Rep 1'

Upvotes: 1

Related Questions