Using INNER JOIN with MySQL on selecting multiple tables?

Question

Hi all,

I have a site where a specific set of data is collected. This is a travel agency website. It is neccesary to determine whether or not an accommodation is still bookable. When I don't select any searchfilters (like destination, classification, facilities etc) I get a working query. The looks like:

SELECT `accommodation` . *
FROM `accommodation`
INNER JOIN (

SELECT `fk_accommodation` , MAX( `dateuntil` ) - ( `releasedays` *60 *60 *24 ) AS `LatestBookableTimestamp`
FROM `priceperiod`
GROUP BY `fk_accommodation`
) AS `pp` ON ( `pp`.`fk_accommodation` = `accommodation`.`id` )
WHERE `pp`.`LatestBookableTimestamp` > UNIX_TIMESTAMP( )
AND `accommodation`.`fk_country` <>0
AND `accommodation`.`classification` >=0
AND `accommodation`.`type` = 'Z'

But when I select a filter (in this case a 'target' filter (children, active, rest, wintersports etc) I get the query:

SELECT `accommodation` . *
FROM `accommodation` , `link_at`
INNER JOIN (

SELECT `fk_accommodation` , MAX( `dateuntil` ) - ( `releasedays` *60 *60 *24 ) AS `LatestBookableTimestamp`
FROM `priceperiod`
GROUP BY `fk_accommodation`
) AS `pp` ON ( `pp`.`fk_accommodation` = `accommodation`.`id` )
WHERE `pp`.`LatestBookableTimestamp` > UNIX_TIMESTAMP( )
AND `accommodation`.`fk_country` <>0
AND `link_at`.`fk_target`
IN ( 10, 2 )
AND `link_at`.`fk_accommodation` = `accommodation`.`id`
AND `accommodation`.`classification` >=0
AND `accommodation`.`type` = 'Z'

Now when I execute this query I get the error: #1054 - Unknown column 'accommodation.id' in 'on clause'. I think this is because another table is used in the FROM clausule. Does anyone have an idea on how to use the INNER JOIN when multiple tables are queried??

Any help would be greatly appreciated.

Ben Fransen

Answer 1

The difficulity caused by a full filtered query...

SELECT 
    `accommodation`.* 
FROM 
    `accommodation` , 
    `link_at` , 
    `priceperiod` , 
    `link_af` 
INNER JOIN ( 
    SELECT 
        `fk_accommodation`, 
        MAX( `dateuntil` ) - ( `releasedays` *60 *60 *24 ) AS `LatestBookableTimestamp` 
    FROM 
        `priceperiod` 
    GROUP BY 
        `fk_accommodation` 
    ) AS `pp` 
    ON (`pp`.`fk_accommodation` = `accommodation`.`id`) 
WHERE 
    `pp`.`LatestBookableTimestamp` > UNIX_TIMESTAMP() 
AND 
    `accommodation`.`fk_country` <> 0 
AND 
    `link_at`.`fk_target` IN (10 , 2 , 1 , 13 , 6 , 3) 
AND 
    `link_at`.`fk_accommodation` = `accommodation`.`id` 
AND 
    ( 
        ( 
            `priceperiod`.`haslogies` = '1' 
        AND 
            ( `datefrom` >= 1288216378 
        OR 
            `dateuntil` <= 1288216378 
            )
         AND 
            `accommodation`.`id` = `priceperiod`.`fk_accommodation` 
        ) 
        OR 
        (
             `priceperiod`.`haslogiesbreakfast` = '1' 
        AND 
        ( 
            `datefrom` >= 1288216378 
        OR 
            `dateuntil` <= 1288216378 
        ) 
        AND 
            `accommodation`.`id` = `priceperiod`.`fk_accommodation` 
        ) 
        OR 
        ( 
            `priceperiod`.`hashalfpension` = '1' 
        AND 
        ( 
            `datefrom` >= 1288216378 
        OR 
            `dateuntil` <= 1288216378 
        ) 
        AND 
        `accommodation`.`id` = `priceperiod`.`fk_accommodation` 
    ) 
    OR 
    ( 
        `priceperiod`.`hasfullpension` = '1' 
        AND 
        ( 
            `datefrom` >= 1288216378 
        OR 
            `dateuntil` <= 1288216378 
        ) 
        AND 
        `accommodation`.`id` = `priceperiod`.`fk_accommodation` 
    ) 
    OR 
    ( 
        `priceperiod`.`hasallinclusive` = '1' 
        AND 
        ( 
            `datefrom` >= 1288216378 
        OR 
            `dateuntil` <= 1288216378 
        ) 
        AND 
            `accommodation`.`id` = `priceperiod`.`fk_accommodation` 
        ) 
    ) 
    AND 
        `accommodation`.`id` IN 
        (
        SELECT 
            `fk_accommodation` 
        FROM 
            `link_af` 
        WHERE 
        (
            `fk_facility` = 13 OR 
            `fk_facility` = 14 OR 
            `fk_facility` = 7 OR 
            `fk_facility` = 27 OR 
            `fk_facility` = 37 OR 
            `fk_facility` = 17 OR 
            `fk_facility` = 24 OR 
            `fk_facility` = 3
        ) 
        GROUP BY 
            `fk_accommodation` 
        HAVING count( fk_accommodation ) =8) 
        AND 
        ( 
            ( 
                `accommodation`.`fk_accommodationtype` = 14 
            ) 
            OR 
            ( 
                `accommodation`.`fk_accommodationtype` = 18 
            ) 
            OR 
            ( 
                `accommodation`.`fk_accommodationtype` = 16 
            ) 
            OR 
            ( 
                `accommodation`.`fk_accommodationtype` = 19 
            ) 
            OR 
            (
                 `accommodation`.`fk_accommodationtype` = 17 
            ) 
        ) 
        AND 
        (
            `accommodation`.`isspecialoffer` = 1 
        OR 
            `accommodation`.`istip` = 1
        ) 
        AND 
            `accommodation`.`classification` >= 4 
        AND `accommodation`.`type` = 'Z'