SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Rehab is for quiters! spartan's Avatar
    Join Date
    Apr 2002
    Location
    Cape Town, South Africa
    Posts
    343
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pairwise subquery

    what is wrong with the following query...
    Code:
    SELECT DISTINCT ordernumber, COUNT(ordernumber) AS Num_of_Brns 
    FROM orderheader 
    WHERE (vendorID, depotnumber) IN 
                    (SELECT vendor_id, depot_id
    		FROM vendordepot 
    		WHERE vendorDepot_id IN (32092,32093)) 
    GROUP BY ordernumber 
    HAVING count(ordernumber) > 1 
    ORDER BY ordernumber
    I get two errors.
    *Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near ','.
    *Server: Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword 'GROUP'.


    I'm using sql server 2000
    Spartan
    ---------------------
    It's like our sergeant told us before one trip into the jungle. Men! Fifty of you are leaving on a mission. Twenty-five of you ain't coming back.
    -Mr.Payne

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You cannot do WHERE ( more than one column ) IN ( SELECT.

    You will have to re-write it like:
    Code:
    WHERE vendorID    IN ( SELECT vendorID )
      AND depotnumber IN ( SELECT depotID  )
    You might try joining to help reduce the query complexity, although I am sure MS SQL's optimizer is smart enough to do that for you.

  3. #3
    Rehab is for quiters! spartan's Avatar
    Join Date
    Apr 2002
    Location
    Cape Town, South Africa
    Posts
    343
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You will have to re-write it like:

    code:
    --------------------------------------------------------------------------------

    WHERE vendorID IN ( SELECT vendorID )
    AND depotnumber IN ( SELECT depotID )
    thats not a pairwise query, so it would not do the trick. the unique combination of vendorID/depotID fields is whats important.

    if you do a non-pairwise query you would simply get two list of values, no telling which ones go together.

    but thanx anyway MattR, I figured it out though...

    the problem, you're right, is the fact that I've got two fields in the where clause.

    the way to solve it though is...
    Code:
    SELECT DISTINCT ordernumber, 
    	COUNT(ordernumber) AS Num_of_Brns 
    FROM orderheader 
    WHERE cast(vendorID as varchar) + '-' + cast(depotnumber as varchar) IN (SELECT cast(vendor_id as varchar) + '-' + cast(depot_id as varchar)
    				FROM vendordepot 
    				WHERE vendorDepot_id IN (32092,32093)
    				) 
    GROUP BY ordernumber 
    HAVING count(ordernumber) > 1 
    ORDER BY ordernumber

    the '-' part is important because otherwise "123" and "456".... would be the same as "12" and "3456"
    Last edited by spartan; Jun 12, 2002 at 04:48.
    Spartan
    ---------------------
    It's like our sergeant told us before one trip into the jungle. Men! Fifty of you are leaving on a mission. Twenty-five of you ain't coming back.
    -Mr.Payne

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does that result in a table scan or can you use an index in this case? I would think no.

  5. #5
    Rehab is for quiters! spartan's Avatar
    Join Date
    Apr 2002
    Location
    Cape Town, South Africa
    Posts
    343
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    dunno actually.
    Spartan
    ---------------------
    It's like our sergeant told us before one trip into the jungle. Men! Fifty of you are leaving on a mission. Twenty-five of you ain't coming back.
    -Mr.Payne

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your best bet would be to re-write it as a JOIN:
    Code:
    SELECT DISTINCT ordernumber,
             COUNT( ordernumber ) AS Num_of_Brns
      FROM orderheader      oh
     INNER JOIN venderdepot vd ON vd.vendor_id = oh.vendorID
                              AND vd.depot_id  = oh.depotnumber
     WHERE vd.vendorDepot_id IN ( 32092, 32093 )
     GROUP BY ordernumber
    HAVING COUNT( ordernumber ) > 1
     ORDER BY ordernumber
    It's a lot cleaner this way and should work like you want.

  7. #7
    Rehab is for quiters! spartan's Avatar
    Join Date
    Apr 2002
    Location
    Cape Town, South Africa
    Posts
    343
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thats nice...
    real nice!

    my hat is off
    Spartan
    ---------------------
    It's like our sergeant told us before one trip into the jungle. Men! Fifty of you are leaving on a mission. Twenty-five of you ain't coming back.
    -Mr.Payne

  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You would also be best to place indexes like so (provided they aren't there already of course:
    Code:
    CREATE INDEX oh_vID_dNo ON orderheader( vendorID, depotNumber, ordernumber )
    -- Order number included so you only have to read the index and not visit the table at all
    CREATE INDEX vd_vID_dID ON vendordepot( vendorDepot_id, vendor_id, depot_id )
    Those should work well, I think.

  9. #9
    Rehab is for quiters! spartan's Avatar
    Join Date
    Apr 2002
    Location
    Cape Town, South Africa
    Posts
    343
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    shot for the help man.
    everythings workin great.
    cheers
    Spartan
    ---------------------
    It's like our sergeant told us before one trip into the jungle. Men! Fifty of you are leaving on a mission. Twenty-five of you ain't coming back.
    -Mr.Payne


Bookmarks

Posting Permissions

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