Select where all in a given list

[BigKahunna]

I have the following query. There are two tables in the DB, one with supplier details and one with supplier rates:-

SELECT SupplierID, RatesTable.* FROM SupplierTable INNER JOIN RatesTable on SupplierTable.SupplierID = RatesTable.SupplierID WHERE RatesTable.RateTypeID IN (11, 14)

At the moment the select picks all the rates where there is an entry in the rates table for EITHER rate 11 or 14. What I want to do is return all the Rates with IDs 11 & 14 for all suppliers who have BOTH rate 11 and 14

Thanks in advance for any help on this one, i'm tearing my hair out trying to get a solution

 

[mrdenny]

I'd recommend join this by joining to the RatesTable twice. Something like this.

select SupplierID, r1.*, r2.*
from SupplierTable
JOIN RatesTable r1 on SupplierTable.SupplierID = RatesTable.SupplierID
   and RatesTable.RateTypeID = 11
JOIN RatesTable r2 on SupplierTable.SupplierID = RatesTable.SupplierID
   and RatesTable.RateTypeID = 14

This should give you the Suppliers that have both rates 11 and 14.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[amrita418]

BigKahunna,

SELECT s.SupplierID, r.* 
FROM SupplierTable s,
RatesTable r 
WHERE s.SupplierID = r.SupplierID 
AND  Rr.RateTypeID IN (11, 14)
AND exists (select 'x' from RatesTable r1
            where r1.SupplierID = s.SupplierID
                  and r1.RateTypeID = 11)
AND exists (select 'x' from RatesTable r2
            where r2.SupplierID = s.SupplierID
                  and r2.RateTypeID = 14)

Regards,
--aa

 

[vongrunt]

Or maybe even:

SELECT SupplierID, RatesTable.* 
FROM SupplierTable 
INNER JOIN
(	SELECT SupplierID
	FROM RatesTable
	WHERE RateTypeID IN (11, 14)
	GROUP BY SupplierID
	HAVING COUNT(DISTINCT RateTypeID) = 2
) R
ON SupplierTable.SupplierID = R.SupplierID

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[BigKahunna]

Cheers guys both of those look like they'd work. The actual queries pretty big and has a few more lookup tables. I'll give both of these a try for performance and post back.

Top Work