ProductID SerialNo InvoiceNo Status 000001 0001 1 PURCHASE 000001 0002 1 PURCHASE 000002 0001 2 PURCHASE 000002 0002 2 PURCHASE 000002 0003 2 PURCHASE 000002 0004 3 ORDER 000003 0001 3 ORDER 000004 0001 3 ORDER 000005 0001 4 PAINTING 000005 0002 4 ORDER 000006 0001 4 ORDER 000007 0001 5 ORDER 000008 0001 5 ORDER 000009 0001 6 PAINTING 000009 0002 6 ORDER
SELECT InvoiceNo from Stock where Status='ORDER' and InvoiceNo not in (select InvoiceNo from Stock where Status <> 'ORDER' )
select * from Stock S INNER JOIN ( select invoiceNo, count(distinct status) from Stock where count(distinct status) = 1 ) T on S.InvoiceNo = T.InvoiceNo where S.Status = 'Order'
SELECT * FROM Stock WHERE Status = 'ORDER' AND ProductID NOT IN(SELECT ProductID FROM Stock WHERE Status <> 'ORDER')
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)