CODEWITHSUNDEEP
mysqlsql
NoobEditor
NoobEditor

Reputation: 15891

select row having one kind of value only while missing other from a pair

I have to select row which has one kind of value in one row but not the other row, both row having a key entity common

Sample model :

+------+---------+---------------+
| key  | status  |   arrival_p   |
+------+---------+---------------+
|  k1  | failure |      came     |
|  k1  | success |      gone     |
|  k2  | failure |      came     |
|  k3  | success |      came     |
|  k3  | failure |      gone     |
|  k4  | success |      came     |
|  k5  | success |      came     |
|  k2  | success |      gone     |
|  k6  | success |      gone     |
+------+---------+---------------+

so in this case, except for k4 and k5, all have come and gone. how can i find folks who have come but not gone??
k6 has just gone, so its an outlier, good to catch it but not immediate priority.

i tried below query but it doesn't work (i know of exact value in actual table which matches my description but below query returns no value at all) :

select ap1.`key`
from `arrival_pattern` ap1
    left join `arrival_pattern` ap2
    on ap1.`key` = ap2.`key`
where ap2.`key` is NULL
    and ap1.`arrival_p` = 'came'
    and ap2.`arrival_p` = 'gone'
limit 10;

any help or pointers in right direction as to what might be wrong in my join is helpful. i am on mysql.

TIA :)

Upvotes: 0

Views: 50

Answers (2)

Robert Kock
Robert Kock

Reputation: 6028

Since both came and gone can appear only once for a specific key, you might as well select the elements for which a single record exists:

SELECT   `key`,
         COUNT(*)
FROM     arrival_pattern
GROUP BY `key`
HAVING   COUNT(*) = 1;

This solves also the second question ('k6 has just gone').

Also, note that key is a Reserved Keyword in MySQL. You should seriously consider naming your column to something else.

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use not exists

select t1.* from  arrival_pattern t1
where not exists ( select 1 
                   from arrival_pattern t2 
                   where t2.key=t1.key 
                         and t2.arrival_p='gone')

you can try below self join

select t1.* arrival_pattern t1 
 left join 
(select key from 
  arrival_pattern t2 where arrival_p='gone'
) t2
 on t1.key=t2.key where t2.key is null

Upvotes: 1

Related Questions