Select non-matching record in same table - SQL

Question

What I am tying to do sounds simple, but cant figure it out. I have a table with a report_number field and a report_type field. The report_number field can have the same "report number" in it as well as the report_type field. I will give some data to better explain what I need to do.

report_number   report_type
1                   A
2                   A
2                   B
1                   A
3                   A
3                   A
3                   A
4                   C
4                   C

I need to query for reports that = 'A', but not the SAME report number that also has a report type B associated with it. The results that I would like to get is report #s 1 and 3.

The report number can have different report_types associated with it.

Thanks!

Reworded: You need all the report_number values that appear in "A" that don't also appear in "B". — Griffin, Mar 09 '12 at 17:35
Is it what Griffin said, or do you want all the report numbers that have a Report_Type "A", but no other report_type at all? It's not clear what the point of the 4/C sample records is. — Levin Magruder, Mar 09 '12 at 17:38

score 4 · Accepted Answer · answered Mar 09 '12 at 17:29

4

try:

select distinct(report_number)
from reports 
where report_type='A' and report_number not in (select report_number from reports where report_type='B')

answered Mar 09 '12 at 17:29

Diego

34,802
21
91
134

1

+1 This is my favorite solution to the anti-semi join question. – Griffin Mar 09 '12 at 17:33

score 0 · Answer 2 · edited May 23 '17 at 12:12

0

SELECT  DISTINCT a.Report_Number
FROM    YourTable a
        LEFT JOIN YourTable b
            ON a.Report_Number = b.Report_Number
                    AND a.Report_Type <> b.Report_Number
                    -- OPTIONAL
                   AND b.Report_Type = 'B'
WHERE   b.Report_Type IS NULL
-- OPTIONAL
AND a.Report_Type = 'A'

EDIT

So far 3 solutions have been posted all adopting a different approach. Check this Link to see which one will suit your RDBMS.

edited May 23 '17 at 12:12

Community

1
1

answered Mar 09 '12 at 17:27

GarethD

68,045
10
83
123

@Diego What RDBMS are you testing this in? This works in SQL-Server, MySQL, Postgresql and Oracle. See this [SQl Fiddle](http://sqlfiddle.com/#!3/3d01c/1) – GarethD Mar 09 '12 at 17:41

Select non-matching record in same table - SQL

2 Answers2