Pranav Unde Pranav Unde - 5 months ago 21
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

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;