Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: SUB SELECT to return two fields

    Hi fella's...

    Having a spot of bother getting this syntax bang on.

    I have the following SQL statement:

    Code:
    "SELECT tmp_orders_messy.* " & _
    "FROM tmp_orders_messy " & _
    "WHERE tmp_orders_messy.[Product Code] NOT IN (" & _
           "SELECT ref_knownCodes.[Exclude], ref_knownCodes.[Custom] " & _
           "FROM ref_knownCodes " & _
           "WHERE ref_knownCodes.[Exclude] IS NOT NULL " & _
           "AND ref_knownCodes.[Custom] IS NOT NULL) " & _
    "AND NOT tmp_orders_messy.[Product Code] LIKE '[0-9]*' " & _
    "ORDER BY tmp_orders_messy.[Order Date], tmp_orders_messy.[Order Number]"
    Which works fine with just 'SELECT ref_knownCodes.[Exclude]', or 'ref_knownCodes.[Custom]', but as I'm sure you can see with both it is invalid, as I'm trying to return two fields from a sub select.

    My question is, how do I achieve this then?

    It's telling me to use an EXIST statement with the FROM statement from the main query, but I can't seem to get it right.

    Any help would be lovely.

    Thanks all!
    Last edited by kez1304; 09-08-11 at 08:59.
    Looking for the perfect beer...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT * 
      FROM tmp_orders_messy 
     WHERE NOT EXISTS
           ( SELECT 1 
               FROM ref_knownCodes
              WHERE [Exclude] = tmp_orders_messy.[Product Code] )
       AND NOT EXISTS
           ( SELECT 1 
               FROM ref_knownCodes
              WHERE [Custom] = tmp_orders_messy.[Product Code] )
    p.s. i found your perfect beer... unfortunately, i drank it last night
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by r937 View Post
    p.s. i found your perfect beer... unfortunately, i drank it last night
    You bastard!!


    Also, I tried:

    Code:
    "WHERE EXISTS (" & _
        "SELECT ref_knownCodes.[Custom], ref_knownCodes.[Exclude] " & _
        "FROM ref_knownCodes " & _
        "WHERE ref_knownCodes.[Custom] IS NOT NULL " & _
        "AND ref_knownCodes.[Custom] IS NOT NULL " & _
        "AND tmp_orders_messy.[Product Code] <> ref_knownCodes.[Custom] " & _
        "AND tmp_orders_messy.[Product Code] <> ref_knownCodes.[Exclude]) "
    Which seems to work, there's a bit too much going on for me to test fully though... Should that do fine you reckon?
    Last edited by kez1304; 09-08-11 at 09:27.
    Looking for the perfect beer...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kez1304 View Post
    Should that do fine you reckon?
    let me paraphrase your query...

    "give me every tmp_orders_messy row where the ref_knownCodes table has at least one row that doesn't contain that tmp_orders_messy product"

    i'm pretty sure that this isn't what you want

    alternatively, i'm not at all sure that this is what you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Makes sense... I've gone with your enlightened code.

    Out of curiosity, what does the "SELECT 1" do?
    Looking for the perfect beer...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kez1304 View Post
    Out of curiosity, what does the "SELECT 1" do?
    it generates a row, consisting of one column, and the value of that column is the number 1

    it actually doesn't matter what the row contains, what the outer query is interested in is whether the row exists
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I see, so it's like a boolean in a way... It just wants to check if anything at all is returned from the subquery, and creating one 'cell' will take the least amount of time to process.

    Thanks buddy, you're a star!

    I think I'll check out your book.
    Looking for the perfect beer...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •