Many to Many search

Databases
1

I am building a apartment search website and I need a script that will search for rentals.

I have 3 tables.

  1. A rentals table. 2. A categories table. 3. And a rentalcategories table.

The rentals are searchable by # of rooms, rental type, amenities and all 3 of which are stored in the rentalcategories table like so:

Now, lets say for example a user if looking for a rental of type House or Apartment or Duplex or Fourplex and it must have 2 bedrooms and it must have a balcony, parking and storage.

How the hell do I write this SQL statment. Currently I have this, which does not work:


SELECT list.id,list.name,list.price,prodcat.category_id 
FROM wp_wpsc_product_list AS list 
INNER JOIN wp_wpsc_item_category_assoc AS prodcat ON list.id = prodcat.product_id 
WHERE list.publish=1 AND list.active=1 
HAVING COUNT(CASE WHEN prodcat.category_id = 18 THEN 'ok' END) = 1 
AND COUNT(CASE WHEN prodcat.category_id IN (11,22,1,24) THEN 'ok' END) > 0 
AND COUNT(CASE WHEN prodcat.category_id = 3 THEN 'ok' END) > 0 
AND COUNT(CASE WHEN prodcat.category_id = 4 THEN 'ok' END) > 0 
AND COUNT(CASE WHEN prodcat.category_id = 9 THEN 'ok' END) > 0

The first HAVING COUNT (18) is for the 2 bedrooms.
The second (11,22,1,24) is for the rental types.
And the last 3 (3),(4) and (9) are for the amenities.

This returns 0 results. Even if I leave the amenities (last 3 COUNT clauses) out completely and only search use the rental type and bedrooms criteria, it returns 0 results everytime.

Please HELP!

2

The tablenames in the query have nothing to do with the tables you described in your post (product_list ?), but maybe this might work (didn’t test it):


SELECT 
    list.id
  , list.name
  , list.price
FROM wp_wpsc_product_list AS list
INNER JOIN wp_wpsc_item_category_assoc AS prodcat 
ON list.id = prodcat.product_id
WHERE list.publish=1 
AND   list.active=1
GROUP BY list.id
HAVING COUNT(CASE WHEN prodcat.category_id = 18 THEN 'ok' END) = 1
AND COUNT(CASE WHEN prodcat.category_id IN (11,22,1,24) THEN 'ok' END) > 0
AND COUNT(CASE WHEN prodcat.category_id = 3 THEN 'ok' END) > 0
AND COUNT(CASE WHEN prodcat.category_id = 4 THEN 'ok' END) > 0
AND COUNT(CASE WHEN prodcat.category_id = 9 THEN 'ok' END) > 0

You didn’t have a GROUP BY. And once you start grouping by product, the prodcat.category_id in the SELECT isn’t very useful (you’d only get one of them) so I got rid of it.

3

Yeah that was pretty dumb of me not to make the table names the same. My post kind of evolved as I was going.

It was also pretty dumb of me to leave the GROUP BY clause out of my post as well, however that was an accident.

Anyways, removing prodcat.category_id from the SELECT statement fixed the issues.

Your a lifesaver… thanks a lot!

Cheers