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]

  On 08/06/2017 at 15:37, Slugsie said:

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

Expand  

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]

  On 08/06/2017 at 15:40, Slugsie said:

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

 

Something along those lines should work.

Expand  

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]

  On 08/06/2017 at 15:47, 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.

Expand  

You're right it works!

 

Thanks,

[Slugsie]

NP