I am building a apartment search website and I need a script that will search for rentals.
I have 3 tables.
- 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:
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.