Combine 2 SELECT statements with filled gaps

Question

I have this SELECT:

SELECT
    m.`maschine-name` AS 'byMaschine',
    q.`mname` AS 'byMName'
FROM
    `qualitaet` q
INNER JOIN
    maschinen m ON m.maschine = q.maschine
WHERE
    q.`status`='1'
GROUP BY
    concat(q.maschine, q.mname)

and get this result:

| maschine-name | mname |
|     TYP 1     |   0   |
|     TYP 2     |   3   |
|     TYP 2     |   4   |
|     TYP 3     |   0   |
|     TYP 4     |   0   |

see SQL Fiddle here

Then i have nearly the same SELECT with additional COUNT and Datefilter:

SELECT
    m.`maschine-name` AS 'byMaschine',
    q.`mname` AS 'byMName',
COUNT(*) AS 'total'
FROM
    `qualitaet` q
INNER JOIN
    maschinen m ON m.maschine = q.maschine
WHERE
    q.`created`>=NOW() - INTERVAL 2 DAY
AND
    q.`status`='1'
GROUP BY
    concat(q.maschine, q.mname)

and get this result:

| maschine-name | mname | total |
|     TYP 2     |   3   |   1   |
|     TYP 3     |   0   |   2   |

see SQL Fiddle here

The 2nd SELECT doesn't give me all information. I need a mix from both SELECTS

The 2nd SELECT should look like this result:

| maschine-name | mname | total |
|     TYP 1     |   0   |   0   |
|     TYP 2     |   3   |   1   |
|     TYP 2     |   4   |   0   |
|     TYP 3     |   0   |   2   |
|     TYP 4     |   0   |   0   |

Is it possible to RIGHT JOIN with 2 SELECTS? Or is there another way to get the result?

Answer 1

Use conditional aggregation:

SELECT m.`maschine-name` AS byMaschine, q.`mname` AS byMName,
       sum(q.created >= NOW() - INTERVAL 2 DAY) as Total
FROM qualitaet q INNER JOIN
     maschinen m
    ON m.maschine = q.maschine
WHERE q.status = '1'
GROUP BY q.maschine, q.mname;

Other suggestions:

• There is no need to concatenate the grouping columns in the GROUP BY -- unless you really, really intend to do this (which I doubt).
• If status is numeric, don't use single quotes for the constant.
• Don't use single quotes for column aliases. In fact, your aliases don't need any quotes at all.