CODEWITHSUNDEEP
sqlsql-server
Robert
Robert

Reputation: 5302

Compare several rows

I have a table with rows with several duplicate entries, e.g.

|[com_enq_id]|  [comtype_type]  |
|  1         | blah             |
|  1         | Found DP Quotes  |
|  1         | Found Quotes     |
|  2         | Found DP Quotes  |
|  2         | blah             |
|  2         | Found DP Quotes  |
|  3         | Found DP Quotes  |
|  3         | Found DP Quotes  |
|  3         | Found DP Quotes  |

I'm trying to select only the rows from the table where the [com_enq_id] does not have both Found DP Quotes and Found Quotes. IDs with one or none of these should be selected but not those with both. (e.g. in this case only 2 and 3 should be selected)

I tried the following which I thought was working at first but it also returns any rows with with both if there are any additional rows for that ID with another [comtype_type] (e.g. blah):

SELECT  
 t1.[com_enq_id] 
 ,t1.[comtype_type], t2.[comtype_type]
FROM    [comments_view] t1
JOIN    [comments_view] t2
ON      t1.[com_enq_id] = t2.[com_enq_id]
WHERE
 ( t1.[comtype_type] = 'Found Quotes' AND  t2.[comtype_type] <> 'Found DP Quotes')
OR
 ( t2.[comtype_type] = 'Found Quotes' AND  t1.[comtype_type] <> 'Found DP Quotes')

Could anyone point out how I need to modify this to exclude those rows / only select [com_enq_id]s which do not have both?

Upvotes: 0

Views: 753

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

I think the easiest and most flexible way to approach this type of question is to use group by and having. To get the list of com_enq_id:

select cv.com_enq_id
from comments_view cv
where comtype_type in ('Found DP Quotes', 'Found Quotes')
group by cv.com_enq_id
having count(distinct comtype_type) <> 2;

You can then get all the rows in various ways. This uses in:

select cv.*
from comments_view cv
where cv.com_enq_id in (select cv.com_enq_id
                        from comments_view cv
                        where comtype_type in ('Found DP Quotes', 'Found Quotes')
                        group by cv.com_enq_id
                        having count(distinct comtype_type) <> 2
                       );

Upvotes: 4

Related Questions