Monday 28 January 2013

How to fetch a record with multiple status' ?

i ve got a question from one of my colleagues and it made me think about it. Question was how to get a record which have more than one status rows in status columns

here is example data

SHIPMENT_CODE            IS_PROCESSED
23456                                              0
23457                                              1
23456                                              1
23458                                              0
23459                                              1
23457                                              0

i have to fetch only those rows which have both status' e.g.

SHIPMENT_CODE           
23456                                           
23457

i know its not the optimal solution but something is better than nothing :D
if anyone got a good solution please share it over here, mine is below.

SELECT

FROM SHIPMENTS 
WHERE SHIPMENT_CODE IN (
                  SELECT SHIPMENT_CODE           

                  FROM SHIPMENTS
                  WHERE IS_PROCESSED= 1    )
AND NAME IN (
                 SELECT SHIPMENT_CODE           

                 FROM SHIPMENTS
                 WHERE
IS_PROCESSED= 0    )                                          

No comments:

Post a Comment