CODEWITHSUNDEEP
sqlsql-server
bobtastic
bobtastic

Reputation: 159

Multiple records with same reference but different statuses, need SQL query to return specific results

First of all I am a bit of a SQL newbie and been learning on the job. I have a data set similar to the table below and want to create a SQL query to return only the items which have been ordered but not delivered.

Ref |   Country |   Ordered     |   Delivered
UPC1    |   AR  |   01/01/2010  |   02/01/2010
UPC1    |   BR  |   01/01/2010  |   02/01/2010
UPC1    |   FR  |   01/01/2010  |   
UPC1    |   UK  |   01/01/2010  |   02/01/2010
UPC1    |   US  |   01/01/2010  |   02/01/2010
UPC2    |   AR  |           |   
UPC2    |   BR  |           |   
UPC3    |   AR  |   01/03/2010  |   
UPC3    |   BR  |   01/03/2010  |   
UPC3    |   DE  |   01/03/2010  |   
UPC3    |   FR  |   01/03/2010  |   
UPC4    |   AR  |   01/01/2010  |   02/01/2010
UPC4    |   BR  |   01/01/2010  |   02/01/2010
UPC4    |   DE  |   01/01/2010  |   
UPC4    |   FR  |   01/01/2010  |   02/01/2010
UPC5    |   AR  |   01/03/2010  |   02/03/2010
UPC5    |   BR  |   01/03/2010  |   02/03/2010
UPC5    |   DE  |   01/03/2010  |   02/03/2010
UPC5    |   FR  |   01/03/2010  |   02/03/2010
UPC6    |   AR  |   01/03/2010  |   
UPC6    |   BR  |   01/03/2010  |   
UPC6    |   DE  |   01/03/2010  |   
UPC6    |   FR  |   01/03/2010  |

Sorry a newbie to posting as well so I don't know how to get the columns to display correctly. The data should paste into Excel or note pad in columns.

I have tried the following: select * from table where ordered <> '' and delivered = '' This returns the records which haven't been ordered but I need to see the records which have not been ordered at all in any country.

Thanks!

Upvotes: 1

Views: 70

Answers (2)

JNK
JNK

Reputation: 65197

You want to use EXISTS and NOT EXISTS:

SELECT *
FROM MyTable T
WHERE EXISTS (SELECT 1 FROM MyTable
              WHERE Ordered <> ''
              AND Ref = T.Ref)
AND NOT EXISTS (SELECT 1 FROM MyTable
               WHERE Delivered <> ''
               AND Ref = T.Ref)

This will give you all records that have a product in REF which has been ordered somewhere but not delivered anywhere.

Upvotes: 1

Prashant16
Prashant16

Reputation: 1526

Not sure but you can try

SELECT DISTINCT * from <table> 
WHERE Ordered IS NOT NULL AND Delivered IS  NULL

Upvotes: 0

Related Questions