MySQL #1054 – Unknown column ‘masterdata_hyung_event.eventId’ in ‘on clause’ on deep join tables

Posted on

Question :

So, I have this query

SELECT * FROM hyung

LEFT JOIN masterdata_hyung_description ON hyung.descriptionId = masterdata_hyung_description.hyungDescriptionId

CROSS JOIN masterdata_hyung_description ON masterdata_hyung_description.eventId = masterdata_hyung_event.eventId

which return #1054 - Unknown column 'masterdata_hyung_event.eventId' in 'on clause'.
I have those three tables, they are (i think) is already related with foreign key. More less like:
table hyung fk with masterdata_hyung_description, and masterdata_hyung_description fk with masterdata_hyung_event. What I want is how can I relate all of them so I can grab data from masterdata_hyung_event from table hyung?. Thank you

EDIT: here’s the link for dump sql. Thank you.

Answer :

I’ll add an answer with some tips that hopefully will make it a bit easier for you to construct and maintain your queries. First, I will suggest that you use aliases for your tables. Your query then becomes:

SELECT * 
FROM hyung as h, masterdata_hyung_description as mhd
JOIN masterdata_hyung_event as mhe 
    ON mhd.eventId = mhe.eventId
WHERE h.Taket = '1822171' 
  AND mhd.hyungDescriptionId = h.descriptionId

Personally, I find that much easier to read. Second, as mentioned I think one should avoid mixing old-style “,” joins with ANSI joins. So I would rewrite your query as:

SELECT * 
FROM masterdata_hyung_description as mhd
JOIN masterdata_hyung_event as mhe 
    ON mhd.eventId = mhe.eventId
JOIN hyung as h
    ON mhd.hyungDescriptionId = h.hyungDescriptionId  
WHERE h.hyungTaket = '1822171' 

I changed some of the identifiers so that they match the fiddle you posted.

After full days my noob brain tried to work hard, I’ve found my solution.

SELECT * FROM hyung, masterdata_hyung_description #this

JOIN masterdata_hyung_event ON masterdata_hyung_description.eventId = masterdata_hyung_event.eventId #and this

WHERE hyung.Taket = '1822171' AND masterdata_hyung_description.hyungDescriptionId = hyung.descriptionId

Not tested in multiple results tho (but it should), but hopefully it works too. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *