sqlmysqlselectcountsql-update

MergingTwo SQL Queries Select and Count in Single statement

I am Working on project, where I am using two queries to get the result i wanted to process.

Query 1

SELECT MID FROM PMS.MACHINE WHERE STATUS=1 AND MID !=0;

Query 2

SELECT MID,COUNT(STATUS) AS QUEUE
FROM PMS.QUEUE
WHERE STATUS=0
GROUP BY MID
ORDER BY MID ASC;

I wanted to merge this two query into a single query, result should be display as MID from PMS.MACHINE and QUEUE Count From PMS.QUEUE Table.

If there is no count then it will result as zero

these are my current results of query 1

enter image description here

query 2 result

enter image description here

in query 2 if the value zero it returns like this

enter image description here

I want result as like

enter image description here

Solution

  • Try this

    SELECT m.MID,
(SELECT COUNT(q.STATUS) FROM PMS.QUEUE q
 where q.MID = m.MID) as QUEUE
FROM PMS.MACHINE m
WHERE m.STATUS = 1 AND m.MID != 0