Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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

Tags:

2

Answers


  1. 0 
    SELECT T1.MID, CASE WHEN T2.QUEUE IS NULL THEN 0 ELSE T2.QUEUE END AS QUEUE
  FROM PMS.MACHINE T1
       LEFT JOIN (SELECT MID, COUNT (STATUS) AS QUEUE
                 FROM PMS.QUEUE
                WHERE STATUS = 0
             GROUP BY MID
             ORDER BY MID ASC) T2
           ON T1.MID = T2.MID
 WHERE T1.STATUS = 1 AND T1.MID != 0
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search