CODEWITHSUNDEEP
mysqlsql
overstack
overstack

Reputation: 109

What is wrong with the view alter?

ALTER ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY
DEFINER VIEW `patient_dentist` AS

SELECT `patients`.`office_id` AS `office_id`
    ,`patients`.`id` AS `patient_id`
    ,`patients`.`dentist_id` AS `dentist_id`
    ,`patients`.`dentist2_id` AS `dentist2_id`
    ,`patients`.`dentist3_id` AS `dentist3_id`
FROM `patients`
WHERE (
        (`patients`.`dentist_id` IS NOT NULL)
        AND isnull(`patients`.`deleted_at`)
        )

UNION

SELECT DISTINCT `reservations`.`office_id` AS `office_id`
    ,`reservations`.`patient_id` AS `patient_id`
    ,`reservations`.`staff_id` AS `dentist_id`
FROM `reservations`
WHERE (
        (`reservations`.`patient_id` IS NOT NULL)
        AND (`reservations`.`staff_id` IS NOT NULL)
        AND isnull(`reservations`.`deleted_at`)
        );

I add two fields in the 'patients' TABLE, the dentist2_id and dentist3_id.

Upvotes: 1

Views: 51

Answers (1)

Madhivanan
Madhivanan

Reputation: 13700

You should add those columns in both part of the code as UNION requires same number of columns. If you do not have those columns try adding some dummy values

ALTER ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY
DEFINER VIEW `patient_dentist` AS

SELECT `patients`.`office_id` AS `office_id`
    ,`patients`.`id` AS `patient_id`
    ,`patients`.`dentist_id` AS `dentist_id`
    ,`patients`.`dentist2_id` AS `dentist2_id`
    ,`patients`.`dentist3_id` AS `dentist3_id`
FROM `patients`
WHERE (
        (`patients`.`dentist_id` IS NOT NULL)
        AND isnull(`patients`.`deleted_at`)
        )

UNION

SELECT DISTINCT `reservations`.`office_id` AS `office_id`
    ,`reservations`.`patient_id` AS `patient_id`
    ,`reservations`.`staff_id` AS `dentist_id`,
    0,
    0
FROM `reservations`
WHERE (
        (`reservations`.`patient_id` IS NOT NULL)
        AND (`reservations`.`staff_id` IS NOT NULL)
        AND isnull(`reservations`.`deleted_at`)
        );

Upvotes: 1

Related Questions