Count number of rows based on a column value from a left join

Question

For a website (travel agency) I'm working on performance boosting. At the moment I'm optimizing the search-module. Amongst other things, I want to optimize a select-box which holds a destination. In the system there are several destinations, but not all destinations have bookable accommodations. So I want to have the list of destinations where at least is 1 accommodation. The destinations table (tablename: geodata) holds 4 types: Country, Area, Region and City. Due to (sort of*) improper development the accommodation table holds 4 foreign keys (fk_country, fk_area, fk_region and fk_city). * Due to growth and incoming requirements each time a feature was developed.

I've tried the following with a left join based on IF statements. But this query does only half the work. I'm most interested the number of accommodations by a given Country, Area, Region or City. At this moment I only get the total number of accommodations. How to tweak this query to correctly get the number of accommodations by a given location type.

My query:

SELECT      geodata.id, 
            geodata.type,
            COUNT(accommodation.id) AS count_accommodations
FROM        geodata
LEFT JOIN   accommodation
ON          IF(geodata.type = 'Country', accommodation.fk_country,
                IF(geodata.type = 'Area', accommodation.fk_area,
                    IF(geodata.type = 'Region', accommodation.fk_region, accommodation.fk_city)
                )
            )
GROUP BY    geodata.id
HAVING      count_accommodations > 0

Current output:

|id|type   |count_accommodations|
---------------------------------
| 1|Country|                   2|
| 2|Area   |                   2|
| 3|Area   |                   2|

Expected output

|id|type   |count_accommodations|
---------------------------------
| 1|Country|                   2|
| 2|Area   |                   1|
| 3|Area   |                   1|

Any help is greatly appreciated.

score 1 · Accepted Answer · answered Oct 30 '12 at 22:10

1

It doesn't work, because you have no join condition. What you do is, evaluate a foreign key (fk_country, fk_area, ...), but you don't use it anywhere.

...
left join accommodation
on (... fk_...) = geodata.???
group by geodata.id
...

answered Oct 30 '12 at 22:10

Olaf Dietsche

72,253
8
102
198

Haha, stupid me! Thanks... I was totally overlooking that part because of the nested IF's. +1 accepted. Thanks for keeping me sharp ;) – Ben Fransen Oct 30 '12 at 22:13

Count number of rows based on a column value from a left join

1 Answers1