I have 2 queries. Query 1:-
select mr.id,count(ml.id) as labor_cnt
from mreq Mr
join mlbr ml on Mr.id = ml.mrid
where Mr.id in(1235,3355)
group by Mr.id
Query 2:-
select mr.id,count(mm.id) as mtrial_cnt
from mreq Mr join mmrm mm on Mr.id = mm.mrid
where Mr.id in(1235,3355)
group by Mr.id
Trying to use union all but won't work. Suggest any alternative to combine them.
Since you're grouping by mr.id in both queries, I assume you want results something like:
mr.id | labor_cnt | mtrial_cnt
--------------------------------
1 | 5 | 3
2 | null | 6
3 | 4 | 2
4 | 3 | null
...
If that's what you're looking for, then you can combine the queries with common table expressions. Something like:
WITH labor as (
SELECT mr.id AS mrid, count(ml.id) AS labor_cnt
FROM mreq mr JOIN mlbr ml ON mr.id = ml.mrid
WHERE mr.id IN (1235, 3355)
GROUP BY mr.id),
mtrial as (
SELECT mr.id AS mrid, count(mm.id) AS mtrial_cnt
FROM mreq mr JOIN mmrm mm ON mr.id = mm.mrid
WHERE mr.id in (1235, 3355)
GROUP BY mr.id)
SELECT COALESCE(l.mrid, m.mrid), l.labor_cnt, m.labor_cnt
FROM labor l FULL OUTER JOIN mtrial m ON mrid
ORDER BY mrid;
Edited to add
It looks like you're using MySQL, and MySQL does not support common table expressions. MySQL does support subqueries, so this may work (note: I haven't verified the syntax, as I don't have a running MySQL instance available):
SELECT COALESCE(l.mrid, m.mrid), l.labor_cnt, m.labor_cnt
FROM
(SELECT mr.id AS mrid, count(ml.id) AS labor_cnt
FROM mreq mr JOIN mlbr ml ON mr.id = ml.mrid
WHERE mr.id IN (1235, 3355)
GROUP BY mr.id) AS labor
FULL OUTER JOIN
(SELECT mr.id AS mrid, count(mm.id) AS mtrial_cnt
FROM mreq mr JOIN mmrm mm ON mr.id = mm.mrid
WHERE mr.id in (1235, 3355)
GROUP BY mr.id) AS mtrial
ON mrid
ORDER BY mrid;
