[MySQL] WHERE clause in nested join

[cyberstalker]

Ik wil een where clause toevoegen op een nested join. Wanneer ik deze echter onder de ON clause zet krijg ik een error 1064 (onjuist gebruik van een reserved keyword).

Zet ik het in de outer body dan krijg ik een 'Unknown column' fout.

Hoe kan ik een where clause toevoegen aan een nested join? De query is als volgt:

SQL:

DELIMITER //
DROP PROCEDURE IF EXISTS `get_day_count`//
CREATE PROCEDURE `get_day_count`
(
    department INT,
    startdate DATE,
    enddate DATE
)
READS SQL DATA
COMMENT 'Get worked days for department, start_date, end_date'
get_day_count_body: BEGIN
    DECLARE i DATE;
    DECLARE shifts TEXT;
    
    CREATE TEMPORARY TABLE `days_in_range` (day DATE);

    SET i = startdate;

    SELECT
        GROUP_CONCAT(`planning_shifts`.`id`)
    INTO
        shifts
    FROM
        `planning_shifts`
    JOIN
        `planning_projects`
    ON
        `planning_shifts`.`fk_pp_id` = `planning_projects`.`id`
    WHERE
        `planning_projects`.`fk_did` = department;

    insert_loop: LOOP
        INSERT INTO `days_in_range` (day) VALUES (i);
        SET i = ADDDATE(i, 1);
        
        IF (i > enddate) THEN
            LEAVE insert_loop;
        END IF;
    END LOOP insert_loop;

    SELECT
        `days_in_range`.`day` AS `day`,
        COUNT(`planning_regular_shifts`.`fk_pid`) AS `regular_shifts`,
        (SELECT
            COUNT(`planning_extra_shifts`.`fk_pid`)
        FROM
            `planning_extra_shifts`
        JOIN
            `planning_shift_status`
        ON
            `planning_extra_shifts`.`status` = `planning_shift_status`.`id`
        WHERE
            `planning_extra_shifts`.`work_date` = `days_in_range`.`day` AND
            FIND_IN_SET(`planning_extra_shifts`.`fk_ps_id`, shifts) AND
            `planning_shift_status`.`worked` = 1) AS `extra_shifts`
    FROM
        `days_in_range`
    LEFT JOIN
    (
        `planning_regular_shifts`
        LEFT JOIN
            `planning_extra_shifts`
        ON
            `planning_regular_shifts`.`day` = DAYOFWEEK(`planning_extra_shifts`.`work_date`) AND
            `planning_regular_shifts`.`fk_ps_id` = `planning_extra_shifts`.`fk_ps_id` AND
            `planning_regular_shifts`.`fk_pid` = `planning_extra_shifts`.`fk_pid`
    )
    ON
        DAYOFWEEK(`days_in_range`.`day`) = `planning_regular_shifts`.`day` AND
        `days_in_range`.`day` BETWEEN `planning_regular_shifts`.`start_date` AND `planning_regular_shifts`.`end_date`
    WHERE
        FIND_IN_SET(`planning_regular_shifts`.`fk_ps_id`, shifts) AND
        `planning_extra_shifts`.`day` IS NULL
    GROUP BY
        `days_in_range`.`day`;

    DROP TABLE `days_in_range`;
END//
DELIMITER ;

Het gaat dus mis bij `planning_extra_shifts`.`day` IS NULL. Dit heb ik als where clause tussen de haakjes geprobeerd, maar deze geeft dat een error 1064.

[winkbrace]

Je gebruikt een inline view, maar niet op de correcte wijze.

SQL:

    FROM
        `days_in_range`
    LEFT JOIN
    (   -- een inline view moet je behandelen als een aparte query
        SELECT * 
        FROM
             `planning_regular_shifts`
        LEFT JOIN
            `planning_extra_shifts`
        ON
            `planning_regular_shifts`.`day` = DAYOFWEEK(`planning_extra_shifts`.`work_date`) AND
            `planning_regular_shifts`.`fk_ps_id` = `planning_extra_shifts`.`fk_ps_id` AND
            `planning_regular_shifts`.`fk_pid` = `planning_extra_shifts`.`fk_pid`
    ) planning_regular_shifts -- en moet een alias hebben waarmee je 'em aan kunt roepen
    ON
        DAYOFWEEK(`days_in_range`.`day`) = `planning_regular_shifts`.`day` AND
        `days_in_range`.`day` BETWEEN `planning_regular_shifts`.`start_date` AND `planning_regular_shifts`.`end_date`

[Verwijderd]

Cyberstalker, Joins gebruiken is handig maar het neemt ook meer process tijd in beslag, niet doen als het niet echt nodig is.

[Woy]

Verwijderd schreef op maandag 09 maart 2009 @ 15:26:
Cyberstalker, Joins gebruiken is handig maar het neemt ook meer process tijd in beslag, niet doen als het niet echt nodig is.

Wat is dat voor kromme redenatie?

Een database gebruik je om relationele data op te slaan. Als je die data weer uit wilt vragen heb je gewoon joins nodig. Stellen dat dat meer process tijd in beslag neemt is nogal vreemd. Meer process tijd als wat?

Tuurlijk moet je niet nodeloos gaan joinen tegen tabellen die je niet nodig hebt, maar dat is ook niet wat er voorgesteld word.

[Verwijderd]

Dat is nou eenmaal wat ik heb geleerd, dat je joins niet zomaar moet gaan gebruiken als het ECHT niet nodig is omdat het extra tijd in beslag neemt. Zeker met grote databases.

[RobIII]

Verwijderd schreef op maandag 09 maart 2009 @ 15:37:
Dat is nou eenmaal wat ik heb geleerd, dat je joins niet zomaar moet gaan gebruiken als het ECHT niet nodig is omdat het extra tijd in beslag neemt. Zeker met grote databases.

Van wie heb je dat geleerd dan? En is dat een betrouwbare bron? Tuurlijk moet je niet 'zomaar' joins gaan gebruiken, net zoals je niet 'zomaar' chloor gebruikt. Maar ieder middel is er met een doel en die joins hebben een duidelijk gespecificeerd doel en zijn in een RDBMS gewoon gangbaar en bedoeld voor het koppelen van gegevens.

[ Voor 27% gewijzigd door RobIII op 09-03-2009 15:38 ]

[cyberstalker]

BazzPsychoNut schreef op maandag 09 maart 2009 @ 14:16:
Je gebruikt een inline view, maar niet op de correcte wijze.

SQL:

    FROM
        `days_in_range`
    LEFT JOIN
    (   -- een inline view moet je behandelen als een aparte query
        SELECT * 
        FROM
             `planning_regular_shifts`
        LEFT JOIN
            `planning_extra_shifts`
        ON
            `planning_regular_shifts`.`day` = DAYOFWEEK(`planning_extra_shifts`.`work_date`) AND
            `planning_regular_shifts`.`fk_ps_id` = `planning_extra_shifts`.`fk_ps_id` AND
            `planning_regular_shifts`.`fk_pid` = `planning_extra_shifts`.`fk_pid`
    ) planning_regular_shifts -- en moet een alias hebben waarmee je 'em aan kunt roepen
    ON
        DAYOFWEEK(`days_in_range`.`day`) = `planning_regular_shifts`.`day` AND
        `days_in_range`.`day` BETWEEN `planning_regular_shifts`.`start_date` AND `planning_regular_shifts`.`end_date`

Ik begrijp niet helemaal waar je heen wilt. Ik gebruik een nested join en geen inline view. Een inline view is geen oplossing, dit gaat namelijk niet omdat je een temporary table maar één keer kunt gebruiken in een statement. (Zie http://dev.mysql.com/doc/...orary-table-problems.html)

Verwijderd schreef op maandag 09 maart 2009 @ 15:26:
Cyberstalker, Joins gebruiken is handig maar het neemt ook meer process tijd in beslag, niet doen als het niet echt nodig is.

Kun je uitleggen wat je bedoelt? Die join die ik gebruik is hier echt nodig. Als je een idee hebt hoe het sneller/eenvoudiger kan sta ik daar volkomen voor open .

EDIT: Was ik nog vergeten, ik gebruik MySQL 5.0.32

[Woy]

Verwijderd schreef op maandag 09 maart 2009 @ 15:37:
Dat is nou eenmaal wat ik heb geleerd, dat je joins niet zomaar moet gaan gebruiken als het ECHT niet nodig is omdat het extra tijd in beslag neemt. Zeker met grote databases.

Het verwerken van relationele data is juist waar RDBMS'en goed in zijn. Daar horen dus ook JOIN's bij. Tuurlijk zul je moeten zorgen dat je database goed in elkaar zit en de juiste indexen/fk's hebben. Ik weet dus niet wie je dat geleerd heeft, maar zonder onderbouwing zou ik het niet al te serieus nemen.

ps:
Tuurlijk zul je altijd goed moeten kijken of je query niet eenvoudiger kan, maar om maar meteen JOIN's helemaal af te schrijven is nogal vreemd.

[KabouterSuper]

Verwijderd schreef op maandag 09 maart 2009 @ 15:26:
Cyberstalker, Joins gebruiken is handig maar het neemt ook meer process tijd in beslag, niet doen als het niet echt nodig is.

Lijkt me onzin....Een goed genormaliseerde database zal juist joins nodig hebben. En daarbij: als je het in SQL kunt doen, doe het dan. Dat is iha sneller dan PL/SQL of andere talen.

[winkbrace]

cyberstalker schreef op maandag 09 maart 2009 @ 15:39:
[...]

Ik begrijp niet helemaal waar je heen wilt. Ik gebruik een nested join en geen inline view. Een inline view is geen oplossing, dit gaat namelijk niet omdat je een temporary table maar één keer kunt gebruiken in een statement. (Zie http://dev.mysql.com/doc/...orary-table-problems.html)

Je hebt gelijk, ik was te snel met reageren. Ik dacht dat je gewoon een syntax fout had.
Wellicht kun je je nested joins wel in een inline view zetten en daarna pas de where toepassen?
Ik heb het proberen uit te werken.
Daarbij heb ik meteen de vrijheid genomen die SELECT in je SELECT gewoon netjes in een JOIN te zetten, zodat je niet voor elke rij die query hoeft uit te voeren, maar gewoon één keer.

schiet maar

SQL:

    SELECT
        a.day,
        COUNT(a.fk_pid) AS regular_shifts,
        extra.extra_shifts
    FROM
    (
        SELECT
            days_in_range.day,
            planning_regular_shifts.fk_pid,
            planning_regular_shifts.fk_ps_id,
            planning_extra_shifts.day AS extra_shifts_day
        FROM
            days_in_range
        LEFT JOIN
        (
            planning_regular_shifts
            LEFT JOIN
                planning_extra_shifts
            ON
                planning_regular_shifts.day = DAYOFWEEK(planning_extra_shifts.work_date) AND
                planning_regular_shifts.fk_ps_id = planning_extra_shifts.fk_ps_id AND
                planning_regular_shifts.fk_pid = planning_extra_shifts.fk_pid
        )
        ON
            DAYOFWEEK(days_in_range.day) = planning_regular_shifts.day AND
            days_in_range.day BETWEEN planning_regular_shifts.start_date AND planning_regular_shifts.end_date
    ) a
    LEFT JOIN   (SELECT pes.work_date,
                        COUNT(pes.fk_pid) extra_shifts
                 FROM   planning_extra_shifts pes
                 JOIN   planning_shift_status pss ON pes.status = pss.id
                 WHERE  FIND_IN_SET(pes.fk_ps_id, shifts) 
                 AND    pss.worked = 1
                ) extra on a.day = extra.work_date
    WHERE FIND_IN_SET(a.fk_ps_id, shifts) 
    AND   a.extra_shifts_day IS NULL
    GROUP BY a.day;

[cyberstalker]

BazzPsychoNut schreef op maandag 09 maart 2009 @ 16:40:
[...]


Je hebt gelijk, ik was te snel met reageren. Ik dacht dat je gewoon een syntax fout had.
Wellicht kun je je nested joins wel in een inline view zetten en daarna pas de where toepassen?
Ik heb het proberen uit te werken.
Daarbij heb ik meteen de vrijheid genomen die SELECT in je SELECT gewoon netjes in een JOIN te zetten, zodat je niet voor elke rij die query hoeft uit te voeren, maar gewoon één keer.

Dankjewel. De query die jij aanbied werkt helaas niet, je maakt hier net als ik namelijk in de eerste subquery gebruik van een nested join. Het probleem is hier dat ik een "ERROR 1054 (42S22): Unknown column 'planning_extra_shifts.day' in 'field list'" melding krijg. Dit komt door de nested join. Die is niet beschikbaar buiten de haakjes van de geneste join, wat op zich prima zou zijn als je er een where clause aan zou toevoegen.

Edit: Heb het nu opgelost met een tweede temporary table. Het is ranzig, maar het werkt en het is snel . Mocht er iemand een briljant plan hebben waarmee het ook kan, dan hoor ik het graag.

SQL:

DELIMITER //
DROP PROCEDURE IF EXISTS `get_day_count`//
CREATE PROCEDURE `get_day_count`
(
    department INT,
    startdate DATE,
    enddate DATE
)
READS SQL DATA
COMMENT 'Get worked days for department, start_date, end_date'
get_day_count_body: BEGIN
    DECLARE i DATE;
    DECLARE shifts TEXT;
    
    CREATE TEMPORARY TABLE `days_in_range` (`day`);
    CREATE TEMPORARY TABLE `day_count` (`day` DATE, `regular_shifts` INT DEFAULT 0);

    SET i = startdate;

    SELECT
        GROUP_CONCAT(`planning_shifts`.`id`)
    INTO
        shifts
    FROM
        `planning_shifts`
    JOIN
        `planning_projects`
    ON
        `planning_shifts`.`fk_pp_id` = `planning_projects`.`id`
    WHERE
        `planning_projects`.`fk_did` = department;

    insert_loop: LOOP
        INSERT INTO `days_in_range` (day) VALUES (i);
        SET i = ADDDATE(i, 1);
        
        IF (i > enddate) THEN
            LEAVE insert_loop;
        END IF;
    END LOOP insert_loop;

    INSERT INTO
        `day_count`
    (
        `day`,
        `regular_shifts`
    )
    SELECT
        `days_in_range`.`day` AS `day`,
        COUNT(`planning_regular_shifts`.`fk_pid`) AS `regular_shifts`
    FROM
        `days_in_range`
    JOIN
        `planning_regular_shifts`
    ON
        DAYOFWEEK(`days_in_range`.`day`) = `planning_regular_shifts`.`day` AND
        `days_in_range`.`day` BETWEEN `planning_regular_shifts`.`start_date` AND `planning_regular_shifts`.`end_date`
    LEFT JOIN
        `planning_extra_shifts`
    ON
        `days_in_range`.`day` = `planning_extra_shifts`.`work_date` AND
        `planning_regular_shifts`.`fk_ps_id` = `planning_extra_shifts`.`fk_ps_id` AND
        `planning_regular_shifts`.`fk_pid` = `planning_extra_shifts`.`fk_pid`
    LEFT JOIN
        `planning_holidays`
    ON
        `days_in_range`.`day` BETWEEN `planning_holidays`.`start_date` AND `planning_holidays`.`end_date` AND
        `planning_regular_shifts`.`fk_pid` = `planning_holidays`.`fk_pid`
    WHERE
        FIND_IN_SET(`planning_regular_shifts`.`fk_ps_id`, shifts) AND
        `planning_extra_shifts`.`id` IS NULL AND
        `planning_holidays`.`id` IS NULL
    GROUP BY
        `days_in_range`.`day`;

    SELECT
        `day_count`.`day`,
        `day_count`.`regular_shifts`,
        IFNULL(COUNT(`planning_extra_shifts`.`id`), 0) AS `extra_shifts`
    FROM
        `day_count`
    JOIN
        `planning_extra_shifts`
    ON
        `day_count`.`day` = `planning_extra_shifts`.`work_date`
    JOIN
        `planning_shift_status`
    ON
        `planning_extra_shifts`.`status` = `planning_shift_status`.`id`
    WHERE
        FIND_IN_SET(`planning_extra_shifts`.`fk_ps_id`, shifts) AND
        `planning_shift_status`.`worked` = 1
    GROUP BY
        `day_count`.`day`,
        `day_count`.`regular_shifts`;

    DROP TABLE `days_in_range`;
    DROP TABLE `day_count`;
END//
DELIMITER ;

[ Voor 62% gewijzigd door cyberstalker op 10-03-2009 13:43 ]