CODEWITHSUNDEEP
mysqlsql
user2976252
user2976252

Reputation: 1

Combine two sql query

can someone help me combine this two sql query to have the result of the first query into a column of the second one , thank you 

SELECT
    `c`.`backoffice_users_id`,
    sum(c.nbr_sous_risques) AS `nbr_sous_risques`
FROM `conseillers_sous_risques` AS `c`
WHERE
    (c.date_du_jour='2013-11-10')
    AND (c.idsous_risques BETWEEN 1 AND 12)
GROUP BY `c`.`backoffice_users_id`


SELECT
    `c`.`backoffice_users_id`,
    `c`.`idsous_risques`,
    `c`.`nbr_sous_risques`,
    `backoffice_users`.`lastname`
FROM `conseillers_sous_risques` AS `c`
INNER JOIN `backoffice_users` ON c.backoffice_users_id=backoffice_users.id
WHERE (c.date_du_jour='2013-11-10')
ORDER BY
    `c`.`backoffice_users_id` ASC,
    `c`.`idsous_risques` ASC

Upvotes: 0

Views: 60

Answers (1)

John Woo
John Woo

Reputation: 263893

Wrap your first query in a subquery and join it using LEFT JOIN so any nonmatching records will still show but with a value of 0 in nbr_sous_risques.

SELECT  c.backoffice_users_id, 
        c.idsous_risques, 
        c.nbr_sous_risques, 
        backoffice_users.lastname,
        COALESCE(d.nbr_sous_risques, 0) nbr_sous_risques
FROM    conseillers_sous_risques AS c
        INNER JOIN backoffice_users 
            ON c.backoffice_users_id = backoffice_users.id 
        LEFT JOIN
        (
            SELECT  c.backoffice_users_id, 
                    SUM(c.nbr_sous_risques) AS nbr_sous_risques 
            FROM    conseillers_sous_risques AS c 
            WHERE   c.date_du_jour='2013-11-10' AND 
                    c.idsous_risques BETWEEN 1 AND 12
            GROUP   BY c.backoffice_users_id
        ) d ON  c.backoffice_users_id = d.backoffice_users_id
WHERE   c.date_du_jour='2013-11-10'
ORDER   BY c.backoffice_users_id ASC, c.idsous_risques ASC

Upvotes: 1

Related Questions