How to get distinct records in following situation

Question

4.00/5 (1 vote)

See more:

In following table with rows specified, I need to get distinct column data e.g.

table name: Stock

C#

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

in this scenario I want result should be return only InvoiceNo which having All productid status as 'Order'
here output must be 3,5

I want this in single query.

Posted 17-Jul-14 23:32pm

nilesh sawardekar

Updated 17-Jul-14 23:35pm

Thanks7872

v2

Add a Solution

Comments

Thanks7872 18-Jul-14 5:36am

Why not Invoice no. 4 and 6? They are also having status= order?

nilesh sawardekar 18-Jul-14 5:37am

4 AND 6 also has status as 'PAINTING'

nilesh sawardekar 18-Jul-14 5:39am

Hows you update it.. :)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Magic Wonder · Answer 1 · 2014-07-18T00:09:00

Solution 1

Hi,

Check this one.

SQL

SELECT InvoiceNo from Stock where Status='ORDER' and InvoiceNo not in (select InvoiceNo from Stock where Status <> 'ORDER' )

Hope this will help you.

Posted 18-Jul-14 0:09am

Magic Wonder

Comments

nilesh sawardekar 19-Jul-14 1:24am

Even its looks much simple and logical..its not working..

_Asif_ · Answer 2 · 2014-07-18T00:25:00

Solution 2

Try this

SQL

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'

Posted 18-Jul-14 0:25am

_Asif_

Comments

nilesh sawardekar 19-Jul-14 1:24am

Join not supporting in access database..

Jinu _George · Answer 3 · 2014-07-18T01:07:00

Solution 3

SELECT * FROM Stock WHERE Status = 'ORDER' AND ProductID NOT IN(SELECT ProductID FROM Stock WHERE Status <> 'ORDER')

Posted 18-Jul-14 1:07am

Jinu _George

Updated 20-Jul-14 21:00pm

v2