Prośba o pomoc w zapytaniu mysql union z joinem

Cześć. Mam pewien problem i mimo, że zrobiłem mały research to nie umiem przeskoczyć jednej rzeczy. Są trzy tabele:

contact_source_of_origin
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| name         | varchar(256) | NO   |     | NULL    |                |
| description  | text         | YES  |     | NULL    |                |
| deleted      | tinyint(1)   | NO   | MUL | 0       |                |
| crmclient_id | int(11)      | YES  | MUL | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Tabeli lead i contact nie wrzucam bo sa tak zaprojektowane, że mają chyba ze 100 kolumn. W tabeli contact jest contact_id i source_of_origin_id, a w tabeli lead jest lead_id i lead_source_id.

Chcę policzyć ilość kontaktów i leadów dla każdego contact_source_of_origin. Udało mi się to, ale otrzymałem wynik na zasadzie takiej, że:

lead_id | contact_id | name
123       | null            | Zrodlo
null       | 123            | Zrodlo

Nie wiem jak sobie z tym poradzić, żeby otrzymać to w jednej linii

To jest moje zapytanie.

SELECT COUNT(lead_id) AS `lead_count`,
       NULL AS contact_count,
       `contact_source_of_origin`.`id`,
       `contact_source_of_origin`.`name`
FROM `lead`
LEFT JOIN `contact_source_of_origin` ON lead_source_id = id
WHERE (contact_source_of_origin.deleted != 1)
GROUP BY `id`

UNION

SELECT NULL AS lead_count,
       COUNT(contact_id) AS `contact_count`,
       `contact_source_of_origin`.`id`,
       `contact_source_of_origin`.`name`
FROM `contact`
LEFT JOIN `contact_source_of_origin` ON source_of_origin_id = id
WHERE (contact_source_of_origin.deleted != 1)
GROUP BY `id`

Może za bardzo kombinuje i można to zrobić prościej.. Robię później z tego paginację, dlatego dobrze by było, żebym jednak miał to sensownie pobrane.

nie znam mysql ale najprosciej by ci bylo zrobic sumowanie na twoim zapytaniu, np tak

select sum(lead_count) as lead_count, sum(contact_count) as contact_count, id, name
from 
(SELECT COUNT(lead_id) AS `lead_count`,
       NULL AS contact_count,
       `contact_source_of_origin`.`id`,
       `contact_source_of_origin`.`name`
FROM `lead`
LEFT JOIN `contact_source_of_origin` ON lead_source_id = id
WHERE (contact_source_of_origin.deleted != 1)
GROUP BY `id`
 
UNION
 
SELECT NULL AS lead_count,
       COUNT(contact_id) AS `contact_count`,
       `contact_source_of_origin`.`id`,
       `contact_source_of_origin`.`name`
FROM `contact`
LEFT JOIN `contact_source_of_origin` ON source_of_origin_id = id
WHERE (contact_source_of_origin.deleted != 1)
GROUP BY `id`)
group by id, name

oczywiscie dostosuj sobie to do mysql tak zeby dzialalo :)

Super, wielkie dzięki. Zmieniłem tylko LEFT JOIN na RIGHT, bo potrzebne mi są wszystkie źródła.

SELECT SUM(lead_count) AS lead_count, SUM(contact_count) AS contact_count, id, name
FROM 
(SELECT COUNT(lead_id) AS `lead_count`,
       NULL AS contact_count,
       `contact_source_of_origin`.`id`,
       `contact_source_of_origin`.`name`
FROM `lead`
RIGHT JOIN `contact_source_of_origin` ON lead_source_id = id
WHERE (contact_source_of_origin.deleted != 1)
GROUP BY `id`
 
UNION
 
SELECT NULL AS lead_count,
       COUNT(contact_id) AS `contact_count`,
       `contact_source_of_origin`.`id`,
       `contact_source_of_origin`.`name`
FROM `contact`
RIGHT JOIN `contact_source_of_origin` ON source_of_origin_id = id
WHERE (contact_source_of_origin.deleted != 1)
GROUP BY `id`) as `sub`
GROUP BY id, name