Pranav Unde Pranav Unde - 1 year ago 63
PHP Question

Combine multiple queries in postgres

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.

Answer Source

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;