CODEWITHSUNDEEP
sqlsql-server
kneidels
kneidels

Reputation: 924

multiple filters per item

i have some apartments that a customer can choose from - based on some parameters.

for example:

he is looking for an apartment, that has:

  1. 4 rooms
  2. a balcony

These parameters, are saved in a table as such (called tblApartmentFilters):

  id (index)     |    apartmentID    |   filterID    | filterOptionID
----------------------------------------------------------------------

where -

  1. The apartmentID matches the apartmentID at the tblApartments table
  2. the filterID defines the question (eg: how many rooms do you want? or do you want a balcony), saved in tblFilters
  3. filterOptionID represents the answer to one of the questions. so in the example above - it would be filterOptionID #7 which is [4 rooms] or item #18 which is [yes - want a balcony]

Now... i need to try create a SELECT query to search for apartments that match the parameters. it works great with one parameter - but as soon as a 2nd comes in - it fails. and i understand why:

SELECT * FROM tblApartments 
INNER JOIN tblApartmentFilters ON tblApartments.aptID = tblApartmentFilters.aptID 
WHERE active = 1 
AND filterID = 1 AND filterOptionID = 7    -- this is for the 4 rooms
AND filterID = 2 AND filterOptionID = 18   -- this is for the balcony

now obviously the above doesnt work for 2 parameters - because the filterID column cannot be 1 and 2 at the same time....

so can someone guide me on how to join onto this filters table, to be able to find one or multiple matches for the parameter requests?

many thanks in advance!

Upvotes: 1

Views: 44

Answers (1)

user2989408
user2989408

Reputation: 3137

You could write something this using EXISTS, assuming you want the apartments that have both 4 rooms and 1 balcony.

SELECT * FROM tblApartments t    
WHERE active = 1 
    AND EXISTS (SELECT f.aptID FROM tblApartmentFilters f 
                WHERE f.filterID = 1 AND f.filterOptionID = 7 AND f.aptID = t.aptID)
    AND EXISTS (SELECT f.aptID FROM tblApartmentFilters f 
                WHERE f.filterID = 2 AND f.filterOptionID = 18 AND f.aptID = t.aptID)

Upvotes: 1

Related Questions