CODEWITHSUNDEEP
phpmysql
John Paul Aquino
John Paul Aquino

Reputation: 23

How to make a query that have Union and join in one query in mysql

How to make a query that have Union and join in one query and I get error on this.

SELECT  * 
FROM    reg_tblvetspecialization 
WHERE   pig = 1 
UNION 
SELECT  * 
FROM    reg_tblvetspecialization 
WHERE   chicken = 1 
LEFT JOIN 
    (
        SELECT  a.system_md_id, a.firstname, a.middlename, 
                a.lastname, a.suffixname, a.profession, a.date_issue, 
                b.Province, b.Municipality, 
                b.company_clinic_name,  b.system_clinic_name_id 
        FROM    reg_tblveterinarian a 
                INNER JOIN reg_tblcliniccompany b 
                    ON a.system_md_id = b.system_md_id 
        WHERE   a.system_md_id = 20041708  
                AND a.deleted = 0 
        GROUP BY a.system_md_id
    );

Upvotes: 0

Views: 45

Answers (1)

cdaiga
cdaiga

Reputation: 4939

Here are some issues with your query:

  • No join criteria for the LEFT JOIN. So Add one.
  • No alias for the right table in the LEFT JOIN. Set one.

That said, your query should look like:

SELECT  * 
FROM    reg_tblvetspecialization 
WHERE   pig = 1 
UNION 
SELECT  * 
FROM    reg_tblvetspecialization 
WHERE   chicken = 1 
LEFT JOIN 
    (
        SELECT  a.system_md_id, a.firstname, a.middlename, 
                a.lastname, a.suffixname, a.profession, a.date_issue, 
                b.Province, b.Municipality, 
                b.company_clinic_name,  b.system_clinic_name_id 
        FROM    reg_tblveterinarian a 
                INNER JOIN reg_tblcliniccompany b 
                    ON a.system_md_id = b.system_md_id 
        WHERE   a.system_md_id = 20041708  
                AND a.deleted = 0 
        GROUP BY a.system_md_id
    ) d                     --adding alias
ON reg_tblvetspecialization.common_column=d.common_column; --adding a join condition

Upvotes: 1

Related Questions