Htykuut

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 :)

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.

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