MYSQL Query with FULL JOIN results in Error Unknown Field-CodePudding

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 I get a MYSQL error 'unknown field' referring to the first select field.

CodePudding user response：

Since MySql doesn't support FULL JOIN, it's using FULL as the alias for tblhydrobill.

But a FULL JOIN can be emulated.

SELECT h.billyear, h.billmonth
, h.KWH as elecconsumption
, h.CurrentCharges as eleccost
, g.cubicft as gasconsumption
, g.total as gascost
FROM tblhydrobill h
LEFT JOIN tblgasdata g 
  ON g.billyear = h.billyear 
 AND g.billmonth = h.billmonth
 AND g.buildingid = 19
WHERE h.meteridnumber = 19

UNION ALL

SELECT g.billyear, g.billmonth
, h.KWH as elecconsumption
, h.CurrentCharges as eleccost
, g.cubicft as gasconsumption
, g.total as gascost
FROM tblgasdata g 
LEFT JOIN tblhydrobill h
  ON h.billyear = g.billyear 
 AND h.billmonth = g.billmonth
 AND h.meteridnumber = 19
WHERE g.buildingid = 19
  AND h.billyear IS NULL

ORDER BY billyear, billmonth

CodePudding user response：

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