Sonny Hansen Sonny Hansen - 2 months ago 9
MySQL Question

MySQL joining 2 queries without matches

my database exist of 2 different tables called

Wardrobe_CloakTable & Wardrobe_ServiceTable


they both have some different values but im trying to collect the SUM of each hours 'price' column.

This query gives me the right result for just Wardrobe_CloakTable

SELECT DATE(delivered) as date, sum(price) as sum, HOUR(delivered) AS hour
FROM Wardrobe_CloakTable
WHERE payingcustomerID = '$payingcustomerID'
AND delivered >= CONCAT(CURDATE() , ' 07:00:00')
AND delivered < CONCAT(CURDATE() , ' 07:00:00') + INTERVAL 1 DAY
GROUP BY DATE(delivered), HOUR(delivered)


This gives me something like

---------------------------------
| date | sum | hour |
| 2016-09-15 | 100 | 9 |
| 2016-09-15 | 200 | 10 |
| 2016-09-15 | 100 | 12 |
| 2016-09-15 | 100 | 18 |
---------------------------------


So only the hours where there is actually data, what i would like to do is have an output like:

-----------------------------------------------------------------
| date | sum(CloakTable) | sum(ServiceTable) | hour |
| 2016-09-15 | 100 | 200 | 9 |
| 2016-09-15 | 200 | 500 | 10 |
| 2016-09-15 | | 400 | 11 |
| 2016-09-15 | 200 | | 14 |
| 2016-09-15 | 100 | 400 | 15 |
| 2016-09-15 | 100 | 200 | 18 |
------------------------------------------------------------------


So far i have tried to create is with a UNION and a FULL JOIN, but as i noticed a full join is not possible in MySQL. Unless you do LEFT then RIGHT.

The closest i have come to something usefull is:

SELECT DATE(delivered) as date,
sum(price) as sum,
HOUR(delivered) as hour

FROM Wardrobe_CloakTable
WHERE payingcustomerID = 2
AND Wardrobe_CloakTable.delivered >= CONCAT(CURDATE() , ' 07:00:00')
AND Wardrobe_CloakTable.delivered < CONCAT(CURDATE() , ' 07:00:00') + INTERVAL 1 DAY
GROUP BY DATE(delivered), HOUR(delivered)
UNION ALL
SELECT DATE(time) as date,
sum(price) as sum,
HOUR(time) as hour
FROM Wardrobe_ServiceTable
WHERE payingcustomerID = 2
AND Wardrobe_ServiceTable.time >= CONCAT(CURDATE() , ' 07:00:00')
AND Wardrobe_ServiceTable.time < CONCAT(CURDATE() , ' 07:00:00') + INTERVAL 1 DAY
GROUP BY DATE(time), HOUR(time)


This gives me:

---------------------------------
| date | sum | hour |
| 2016-09-15 | 100 | 9 | (Cloaktable)
| 2016-09-15 | 200 | 10 | (Cloaktable)
| 2016-09-15 | 100 | 9 | (service)
| 2016-09-15 | 100 | 11 | (service)
---------------------------------


Schema

CREATE TABLE `Wardrobe_CloakTable` (
`ID` int(64) NOT NULL,
`payingcustomerID` int(11) NOT NULL,
`deviceID` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`terminalnumber` int(11) NOT NULL,
`qrcode` varchar(64) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL,
`cloakroomsection` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`cloakroomnumber` int(11) NOT NULL,
`isbag` tinyint(1) NOT NULL,
`price` int(11) NOT NULL,
`delivered` datetime NOT NULL,
`collected` datetime DEFAULT NULL,
`reservedtime` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `Wardrobe_ServiceTable` (
`ID` int(11) NOT NULL,
`payingcustomerID` int(11) NOT NULL,
`qrcode` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
`time` datetime NOT NULL,
`price` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Answer

Sonny,

I got a working request. Not 100% sure it is the best way to handle it, but at least it's working:

SELECT date_buy, hour_buy, sum(sum_cloak) AS `sum(CloakTable)`, sum(sum_service) AS `sum(ServiceTable)` FROM (
    SELECT DATE(wct.delivered) as date_buy, HOUR(wct.delivered) AS hour_buy, sum(wct.price) AS sum_cloak, 0 as sum_service FROM Wardrobe_CloakTable wct
    WHERE wct.payingcustomerID = 2 
        AND wct.delivered >= CONCAT(CURDATE(), ' 07:00:00')
        AND wct.delivered < CONCAT(CURDATE(), ' 07:00:00') + INTERVAL 1 DAY
    GROUP BY date_buy, hour_buy

    UNION ALL

    SELECT DATE(wst.time) as date_buy, HOUR(wst.time) AS hour_buy, 0 AS sum_cloak, sum(wst.price) as sum_service
    FROM Wardrobe_ServiceTable wst
    WHERE wst.payingcustomerID = 2
        AND wst.time >= CONCAT(CURDATE(), ' 07:00:00')
        AND wst.time < CONCAT(CURDATE(), ' 07:00:00') + INTERVAL 1 DAY
    GROUP BY date_buy, hour_buy
) fullTab
GROUP BY date_buy, hour_buy
Comments