The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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 05: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

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 )