0

I've a table "location" with the structure:

id  | property_id | location_type
1   | 1           | 1
2   | 1           | 2
3   | 2           | 1
4   | 3           | 2
5   | 4           | 1
6   | 4           | 2

I've another table "amenities" with the structure:

id  | property_id | amenity_type
1   | 1           | 1
2   | 1           | 3
3   | 2           | 2
4   | 3           | 4
5   | 4           | 1
6   | 4           | 3

I've another table "property" with the structure:

id  | property_id | property_type
1   | 1           | 2
2   | 1           | 3
3   | 2           | 2
4   | 3           | 4
5   | 4           | 2
6   | 4           | 3

id - is the primary key of the respective table. property_id is the property ID of my database (foreign key). location_type is 

beach (value - 1), mountain (value - 2).

amenity_type is car (value - 1), bike (value - 2), football (value - 3).

property_type is villa (value - 2), house (value - 3)

I'm using the following SQL query to select the property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1 i.e. a property has beach and mountains and car and villa and house:

SELECT p.id  
FROM 
    property AS p           
  JOIN
    location AS l1
        ON  l1.property_id = p.id  
        AND l1.location_type = 1 
  JOIN
    location AS l2
        ON  l2.property_id = p.id  
        AND l2.location_type = 2 
  JOIN                      
    amentities AS a1
        ON  a1.property_id = p.id
        AND a1.amenity_type = 2                 
  JOIN
    properties AS p1
        ON  p1.property_id = p.id  
        AND p1.property_type = 3 
  JOIN
    properties AS p2 
        ON  p2.property_id = p.id  
        AND p2.property_type = 1

suppose I get the count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) as 1500. I need to get the count with same condition and other property_type, location_type, amenity_type.

But I'm not able to get the count for the following conditions using the above query:

  1. count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND location_type = 3

  2. count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND location_type = 4

  3. count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND amenity_type = 2

  4. count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND amenity_type = 3

Is there any efficient way to get the count with different location_type, amenity_type, etc.

Please refer to my earlier question - MySQL query - complex searching condition

Improve this question
2
  • so you want the count for some base case and also the count you would get in each different case where you hold everything fixed, but vary one parameter? so this is going to produce a lot of different counts? do you really need all these different combinations? or is it only some subset? Commented Aug 18, 2011 at 19:22
  • Yes, I need the base count and counts based on the base condition and adding another parameter so that I can show the users how many properties are available if you select this location_type as well. We really need to show all different combinations. Something very similar to homeaway.com/search, here you can see the count on the left hand filters. Commented Aug 18, 2011 at 22:37

3 Answers 3

Reset to default
3

You can use your base query to get all property.id (and count them) and subqueries in the SELECT list to count the additional conditions, like this:

SELECT COUNT(*) AS BaseCount
     , ( SELECT COUNT(*)
         FROM location AS l3
         WHERE l3.property_id = p.id  
           AND l3.location_type = 3
       ) AS CountLocation3
     , ( SELECT COUNT(*)
         FROM location AS l4
         WHERE l4.property_id = p.id  
           AND l4.location_type = 4
       ) AS CountLocation4
     , ( SELECT COUNT(*)
         FROM amenities AS a2
         WHERE a2.property_id = p.id  
           AND a2.amenity_type = 2
       ) AS CountAmenity4
     , ...
FROM 
    property AS p           
  JOIN
    location AS l1
        ON  l1.property_id = p.id  
        AND l1.location_type = 1 
  JOIN
    location AS l2
        ON  l2.property_id = p.id  
        AND l2.location_type = 2 
  JOIN                      
    amentities AS a1
        ON  a1.property_id = p.id
        AND a1.amenity_type = 1                 
  JOIN
    properties AS p3
        ON  p3.property_id = p.id  
        AND p3.property_type = 3 
  JOIN
    properties AS p1 
        ON  p1.property_id = p.id  
        AND p1.property_type = 1
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Do you think this is the only and the best way to solve it? Comments would be helpful.
@dang: No, I'm sure there are other ways. Which one is better for performance though may also depend on your tables sizes, distibutions (example: how many different locations per property?) in the tables data (and assuming that you have indexed properly).
One way for example to rewrite the (base) query is to use WHERE with an EXISTS subquery instead of a JOIN.
As for the subqueries in the SELECT list, if the base query returns 2k records, it would probably not be slow. But if the base query returns 100k records, then it may be slow. You need to test. If you have such possible outcome, you can also try running one query for every count you need.
On an average, I've around 3 location_types per property but it certainly varies on exceptional cases. I have around 5 amenity_types per property and 4 property_types per property. I've around 200000 properties in the database. Please advise. I've indexed properly is what I feel but trying to reduce the load time considerably.
|
0

your queries are failing due to this:

property_type = 3 AND property_type = 1

a single field cannot have two values at the same time. It should be

... AND ... (property_type = 3 OR property_type = 1) AND ...

or slightly more readable:

... AND ... property_type IN (1, 3) AND ...

The same holds for the location_type AND amenity_type fields.

Improve this answer

1 Comment

sorry, I din't clarify it earlier but the count conditions are not queries, they're just to define that I want count of property_id which has location_type = 1, location_type = 2, amenity_type = 1, property_type = 3, property_type = 1 (all compulsory conditions) - this is a search application.
0

I'm having a little difficulty understanding the question, but I think you're asking about getting additional information about the properties that match your base filter.

One approach you could try is something like this:

SELECT p.id, l.location_type, a.amenity_type, count(*) property_count
FROM (
    SELECT p.id
    FROM property AS p           
        INNER JOIN location AS l1 ON l1.property_id = p.id AND l1.location_type = 1 
        INNER JOIN location AS l2 ON l2.property_id = p.id AND l2.location_type = 2 
        INNER JOIN amentities AS a1 ON  a1.property_id = p.id AND a1.amenity_type = 2                 
        INNER JOIN properties AS p1 ON  p1.property_id = p.id AND p1.property_type = 3 
        INNER JOIN properties AS p2 ON  p2.property_id = p.id AND p2.property_type = 1
) p
INNER JOIN location AS l ON l.property_id = p.id
INNER JOIN amentities AS a ON  a.property_id = p.id
GROUP BY p.id, l.location_type, a.amenity_type
WITH ROLLUP

You can expand on the join list and conditions from there.

EDIT: Based on your comment, I think you need the GROUP BY logic added...

Improve this answer

3 Comments

Thanks Doug, but I don't want to select the properties but I'd like to select the count with my base filter and each location_type, amenity_type.
Sounds like you need to use GROUP BY ... WITH ROLLUP.
Rethinking the above, you may want to remove p.id from the SELECT list and GROUP BY clause to count all properties within the base filter (instead of the location/amenity count for each property, which doesn't seem to be helpful).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.