SQL Query Help

[limok]

Hi 

 

I have the below data

 

Order--Code--Fulfilled

AAA--123--Yes

BBB--123--Yes

BBB--456--No

CCC--456--Yes

CCC--123--Yes

 

From the above data how can I just get the orders that have been fully fulfilled (BBB has not been fulfilled so should be filtered out)?

 

thanks!

[Slugsie]

SELECT * FROM <tablename> WHERE Fulfilled = 'Yes'

*EDIT* Just noticed that BBB is 'partially' fulfilled, give me a minute on that one...

[limok]

Just now, Slugsie said:

SELECT * FROM <tablename> WHERE Fulfilled = 'Yes'

Not quite - this will also show me order BBB, which isn't fully fulfilled.

[Slugsie]

SELECT * FROM <tablename> WHERE Order NOT IN (SELECT Order FROM <tablename> WHERE Fulfilled = 'No')

 

Something along those lines should work.

[limok]

4 minutes ago, Slugsie said:

SELECT * FROM <tablename> WHERE Order NOT IN (SELECT Order FROM <tablename> WHERE Fulfilled = 'No')

 

Something along those lines should work.

That will give the same result...

[Slugsie]

No it won't. Inner query finds all Orders that aren't fulfilled (which will include BBB), then outer query returns any orders that aren't on that list, so BBB will be excluded.

[IceBreakerG]

Slugsie's response should work.  In the sub-query, you'll get BBB because it hasn't been fully fulfilled.  So it won't select BBB in the main query, just AAA and CCC.

[limok]

3 minutes ago, Slugsie said:

No it won't. Inner query finds all Orders that aren't fulfilled (which will include BBB), then outer query returns any orders that aren't on that list, so BBB will be excluded.

You're right it works!

 

Thanks,

[Slugsie]

NP