I have two tables called meals and nolivemeals. if ther is no meals transaction from the Meals table, i need to take the relevant record from the notlivemeals table. i got two sets of query one return values from meals and another one from notlive meals. how to combine in to one?
Query A:
SELECT name
, cashpupil AS CASHPUPILMEALS
, cashpupil * price_pupil AS CASHPUPILMEALSAMOUNT
, cashnursery AS CASHNURSERYMEALS
, cashnursery * price_nursery AS CASHNURSERYMEALSAMOUNT
, cashadult AS CASHADULTMEALS
, cashadult * price_adult AS CASHADULTMEALSAMOUNT
, freepupil AS PUPILFREEMEALS
, freepupil * price_pupil AS PUPILFREEMEALSAMOUNT
, freenursery AS NURSERYFREEMEALS
, freenursery * price_nursery as NURSERYFREEMEALSAMOUNT
, freeadult AS ADULTFREEMEALS
, freeadult * price_adult AS ADULTFREEMEALSAMOUNT
, CONCAT( '£', FORMAT(COALESCE(o.sum_o_amount,0),2)) AS OTHERCASH
, CONCAT( '£', FORMAT(COALESCE(nm.cashreceived,0),2)) AS STDCASHRECEIVED
, 0
, 0
, CONCAT( '£', FORMAT(COALESCE(BK.sum_act_spent,0),2)) AS ACTUALLSPENT
, CONCAT( '£', FORMAT(COALESCE(INV.sum_inv_amount,0),2)) AS INVOICESTOT
, COALESCE(WG.sum_tot_hours,0) AS sum_tot_hours
, 0
FROM notlivemeals AS nm
INNER
JOIN schools AS s
ON s.id = nm.school_id
LEFT
JOIN ( SELECT school_id
, SUM(amount) AS sum_o_amount
FROM othercashrecieved
WHERE weekStart = '20110130'
GROUP
BY school_id ) as o
ON o.school_id = s.id
LEFT
JOIN( SELECT cashspent.school_id,
SUM(amount) as sum_ocs_amount
FROM cashspent
WHERE weekstart = '20110130'
GROUP
BY school_id ) as CP
ON CP.school_id = s.id
LEFT
JOIN( SELECT banking.school_id
, (SUM(cash)
+ SUM(cheques)) AS sum_act_spent
FROM banking
WHERE weekstart = '20110130'
GROUP
BY school_id ) as BK
ON BK.school_id = s.id
LEFT
JOIN( SELECT invoices.school_id,
SUM(invAmount) as sum_inv_amount
FROM invoices
WHERE weekEnterDate = '20110130'
GROUP
BY school_id ) as INV
ON INV.school_id = s.id
LEFT
JOIN( SELECT wages.school_id,
SUM(sunHrs+monHrs+tueHrs+wedHrs+thuHrs+friHrs+satHrs) as sum_tot_hours
FROM wages
WHERE weekStart = '20110130'
GROUP
BY school_id ) as WG
ON WG.school_id = s.id
WHERE nm.startdate = '20110130' AND nm.enddate = '20110205'
AND s.notlive = '1'
AND s.notininvoice = '0'
GROUP
BY s.name
ORDER
BY s.name;
Query B:
SELECT s.name
, COUNT(CASE WHEN typeCode = '1'
AND pp.year != '9'
THEN 1 ELSE NULL END) AS CASHPUPILMEALS
, CONCAT( '£', FORMAT(
SUM(CASE WHEN typeCode = '1'
AND pp.year != '9'
THEN price ELSE 0 END),2)) AS CASHPUPILMEALSAMOUNT
, COUNT(CASE WHEN typeCode = '1'
AND pp.year = '9'
THEN 1 ELSE NULL END) AS CASHNURSERYMEALS
, CONCAT( '£', FORMAT(
SUM(CASE WHEN typeCode = '1'
AND pp.year = '9'
THEN price ELSE 0 END),2)) AS CASHNURSERYMEALSAMOUNT
, COUNT(CASE WHEN typeCode in ('3')
THEN 1 ELSE NULL END) AS CASHADULTMEALS
, CONCAT( '£', FORMAT(
SUM(CASE WHEN typeCode IN ('3')
THEN price ELSE 0 END),2)) AS CASHADULTMEALSAMOUNT
, COUNT(CASE WHEN typeCode = '2'
AND pp.year != '9'
THEN 1 ELSE NULL END) AS PUPILFREEMEALS
, CONCAT( '£', FORMAT(
SUM(CASE WHEN typeCode = '2'
AND pp.year != '9'
THEN price ELSE 0 END),2)) AS PUPILFREEMEALSAMOUNT
, COUNT(CASE WHEN typeCode = '2'
AND pp.year = '9'
THEN 1 ELSE NULL END) AS NURSERYFREEMEALS
, CONCAT( '£', FORMAT(
SUM(CASE WHEN typeCode = '2'
AND pp.year = '9'
THEN price ELSE 0 END),2)) AS NURSERYFREEMEALSAMOUNT
, COUNT(CASE WHEN typeCode IN ('4')
THEN 1 ELSE NULL END) AS ADULTFREEMEALS
, CONCAT( '£', FORMAT(
SUM(CASE WHEN typeCode IN ('4')
THEN price ELSE 0 END),2)) AS ADULTFREEMEALSAMOUNT
, CONCAT( '£', FORMAT(COALESCE(o.sum_o_amount,0),2)) AS OTHERCASH
, CONCAT( '£', FORMAT(COALESCE(CR.sum_scr_amount,0),2)) AS STDCASHRECEIVED
, CONCAT( '£', FORMAT(COALESCE(CR.sum_acr_amount,0),2)) AS ADTCASHRECEIVED
, CONCAT( '£', FORMAT(COALESCE(CP.sum_ocs_amount,0),2)) AS OTHERCASHCOLLECTED
, CONCAT( '£', FORMAT(COALESCE(BK.sum_act_spent,0),2)) AS ACTUALLSPENT
, CONCAT( '£', FORMAT(COALESCE(INV.sum_inv_amount,0),2)) AS INVOICESTOT
, COALESCE(WG.sum_tot_hours,0) AS sum_tot_hours
, COUNT(CASE WHEN typeCode = '1'
AND pp.year != '9'
THEN 1 ELSE NULL END) +
COUNT(CASE WHEN typeCode = '1'
AND pp.year = '9'
THEN 1 ELSE NULL END) +
COUNT(CASE WHEN typeCode in ('3')
THEN 1 ELSE NULL END) AS SUBTOTAL
FROM meals AS m
INNER
JOIN parents AS p
ON p.id = m.parent_id
INNER
JOIN pupils AS pp
ON pp.id = m.pupil_id
INNER
JOIN schools AS s
ON s.id = m.school_id
LEFT
JOIN ( SELECT school_id
, SUM(amount) AS sum_o_amount
FROM othercashrecieved
WHERE weekStart = '20110130'
GROUP
BY school_id ) as o
ON o.school_id = s.id
LEFT
JOIN( SELECT cashreceived.school_id
, SUM(CASE WHEN pupils.year !=8
THEN amount ELSE 0 END) AS sum_scr_amount
, SUM(CASE WHEN pupils.year =8
THEN amount ELSE 0 END) AS sum_acr_amount
FROM cashreceived,pupils
WHERE cashReceived.pupil_id=pupils.id
AND(cashReceived.date BETWEEN '20110130' AND '20110205')
GROUP
BY school_id ) as CR
ON CR.school_id = s.id
LEFT
JOIN( SELECT cashspent.school_id,
SUM(amount) as sum_ocs_amount
FROM cashspent
WHERE weekstart = '20110130'
GROUP
BY school_id ) as CP
ON CP.school_id = s.id
LEFT
JOIN( SELECT banking.school_id
, (SUM(cash)
+ SUM(cheques)) AS sum_act_spent
FROM banking
WHERE weekstart = '20110130'
GROUP
BY school_id ) as BK
ON BK.school_id = s.id
LEFT
JOIN( SELECT invoices.school_id,
SUM(invAmount) as sum_inv_amount
FROM invoices
WHERE weekEnterDate = '20110130'
GROUP
BY school_id ) as INV
ON INV.school_id = s.id
LEFT
JOIN( SELECT wages.school_id,
SUM(sunHrs+monHrs+tueHrs+wedHrs+thuHrs+friHrs+satHrs) as sum_tot_hours
FROM wages
WHERE weekStart = '20110130'
GROUP
BY school_id ) as WG
ON WG.school_id = s.id
WHERE m.date BETWEEN '20110130' AND '20110205'
AND notlive = 0
AND notininvoice = 0
GROUP
BY s.name
ORDER
BY s.name