Select rows where column value is unique in relation to a column-CodePudding

Given the table

| eventId | orderId | id
| 18      | 20      | 1
| 19      | 20      | 2
| 20      | 21      | 3
| 20      | 21      | 4

I want to select only the rows where, given a specific eventId (let's say 18), its orderId has no other eventId associated to it.

So in the case above it would return nothing for eventId 18 or 19, but for eventtId 20 it would return the two rows.

CodePudding user response：

SELECT *
  FROM tbl t1
 WHERE NOT EXISTS (SELECT *
                     FROM tbl t2
                    WHERE t1.orderId  = t2.orderId
                      AND t1.eventId != t2.eventId)

SQL Fiddle: http://sqlfiddle.com/#!9/bf1422a/1

CodePudding user response：

Use aggregation:

SELECT MAX(eventId) eventId, orderId
FROM tablename
GROUP BY orderId
HAVING COUNT(*) = 1; -- or HAVING COUNT(DISTINCT eventId) = 1

The condition HAVING COUNT(*) = 1 will return only the rows where the orderId is related to only one eventId and this would be equal to MAX(eventId) (since it is the only one).

Use the condition HAVING COUNT(DISTINCT eventId) = 1 only if the combination of eventId and orderId is not unique.

CodePudding user response：

you can use aggregation

  SELECT orderId
  FROM tbl t1
 group by orderId
 having count(distinct eventId)=1

CodePudding user response：

Do a GROUP BY, use HAVING to make sure an eventId doesn't share its orderId:

select eventId, orderId
from
(
    select min(eventId) eventId, orderId
    from tbl
    group by orderId
    having count(*) = 1
) dt

where eventId = 18;

LEFT JOIN version:

select t1.eventId, t1.orderId
from tbl t1
left join tbl t2 on t1.orderId = t2.orderId and t1.eventId <> t2.eventId
where t2.orderId is null

  and t1.eventId = 18

CodePudding user response：

If the table is called tbl this query should work

select t.orderId, t.eventId from tbl t join
( select orderId, count(*) tm from tbl GROUP by orderId) a 
on t.orderId = a.orderId
where a.tm = 1
 and t.eventId = 20

with having

select t.orderId, t.eventId from tbl t join
( select orderId, count(*) tm from tbl GROUP by orderId having count(*) = 1) a 
on t.orderId = a.orderId