SQL query look for exact match using inner join

Question

Hi!

I have 2 tables in SQL Server 2005 db with structures represented as such:

CAR: CarID bigint, CarField bigint, CarFieldValue varchar(50);

TEMP: CarField bigint, CarFieldValue varchar(50);

Now the TEMP table is actually a table variable containing data collected through a search facility. Based on the data contained in TEMP, I wish to filter out and get all DISTINCT CarID's from the CAR table exactly matching those rows in the TEMP table. A simple Inner Join works well, but I want to only get back the CarID's that match ALL the rows in TEMP exactly. Basically, each row in TEMP is supposed to be denote an AND filter, whereas, with the current inner join query, they are acting more like OR filters. The more rows in TEMP, the less rows I expect showing in my result-set for CAR. I hope Im making sense with this...if not please let me know and I'll try to clarify.

Any ideas on how I can make this work? Thank u!

Answer 1

You use COUNT, GROUP BY and HAVING to find the cars that have exactly that many mathicng rows as you expect:

   select CarID
   from CAR c  
   join TEMP t on c.CarField = t.CarField and c.CarFieldValue = t.CarFieldValue
   group by CarID
   having COUNT(*) = <the number you expect>;

You can even make <the number you expect> be a scalar subquery like select COUNT(*) from TEMP.

Answer 2

SELECT *
FROM (
SELECT CarID,
COUNT(CarID) NumberMatches
FROM CAR c INNER JOIN
TEMP t ON c.CarField = t.CarField
AND c.CarFieldValue = t.CarFieldValue
GROUP BY CarID
) CarNums
WHERE NumberMatches = (SELECT COUNT(1) FROM TEMP)

Answer 3

Hrm...

;WITH FilteredCars
AS
(
  SELECT C.CarId
  FROM Car C
  INNER JOIN Temp Criteria 
     ON C.CarField = Criteria.CarField 
    AND C.CarFieldValue = Critera.CarFieldValue
  GROUP BY C.CarId
  HAVING COUNT(*) = (SELECT COUNT(*) FROM Temp)
)
SELECT *
FROM FilteredCars F
INNER JOIN Car C ON F.CarId = C.CarId

The basic premise is that for ALL criteria to match an INNER JOIN against your temp table must produce as many records as there are within that table. The HAVING clause at the end of the FilteredCars query should widdle the results down to those that match all criteria.

Answer 4

Haven't tested this, but I don't think you need a count to do what you want. This query ought to be substantially faster because it avoids a potentially huge number of counts. This query finds all the cars which are missing a value and then filters them out.

select distinct carid from car where carid not in
(
select
  carid
from
  car c
  left outer join temp t on
    c.carfield = t.carfield
    and c.carfieldvalue = t.carfieldvalue
where
  t.carfield is null
)