I'm trying to join two tables to show all rows that match the where clause, not just the ones that match the join. It's two tables of bills and I am trying to merge them so that each row with matching year and month from both tables has a few fields from each.
SELECT
tblhydrobill.billyear, tblhydrobill.billmonth, tblhydrobill.KWH as elecconsumption, tblhydrobill.CurrentCharges as eleccost,
tblgasdata.cubicft as gasconsumption, tblgasdata.total as gascost
FROM tblhydrobill
FULL JOIN tblgasdata ON tblhydrobill.billyear = tblgasdata.billyear and tblhydrobill.billmonth = tblgasdata.billmonth
WHERE tblhydrobill.meteridnumber = 19 and tblgasdata.buildingid = 19
ORDER BY tblhydrobill.billyear asc, tblhydrobill.billmonth asc
LEFT JOIN executes properly, but only shows results where data exists for month/year on both tables. I am trying to get it to return all rows, it's ok if there are null fields on one side of the join if there is no match on the other side and vice-versa.
Using FULL JOIN I get the following MySQL error:
Error Code: 1054. Unknown column 'tblhydrobill.billyear' in 'field list'
Update:
FULL JOIN is not supported yet by MySQL as pointed out by @jarlh, thanks.
You can generate a list of year-month pairs that are present in one or both tables using union, then left join the two tables with that result:
select *
from (
select billyear, billmonth from tblhydrobill where meteridnumber = 19
union
select billyear, billmonth from tblgasdata where buildingid = 19
) as ym
left join tblhydrobill on tblhydrobill.billyear = ym.billyear and tblhydrobill.billmonth = ym.billmonth and tblhydrobill.meteridnumber = 19
left join tblgasdata on tblgasdata.billyear = ym.billyear and tblgasdata.billmonth = ym.billmonth and tblgasdata.buildingid = 19
order by ym.billyear, ym.billmonth
Note that it is possible to build ym list manually e.g.:
from (
select 2022, 1 union
select 2021, 12 union
select 2021, 11
) as ym