• Sign in to Neowin Faster!

    Create an account on Neowin to contribute and support the site.

  • 0
Sign in to follow this  

SQL Query Help

Question

limok    60

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!

Share this post


Link to post
Share on other sites

8 answers to this question

Recommended Posts

  • 0
Slugsie    963

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

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

Share this post


Link to post
Share on other sites
  • 0
limok    60
Just now, Slugsie said:

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

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

Share this post


Link to post
Share on other sites
  • 0
Slugsie    963

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

 

Something along those lines should work.

  • Like 1

Share this post


Link to post
Share on other sites
  • 0
limok    60
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...

Share this post


Link to post
Share on other sites
  • 0
Slugsie    963

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.

  • Like 1

Share this post


Link to post
Share on other sites
  • 0
IceBreakerG    142

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.

Share this post


Link to post
Share on other sites
  • 0
limok    60
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,

Share this post


Link to post
Share on other sites
  • 0
Slugsie    963

NP :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.