0

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.

2
  • 1
    First, post create table statements so that it is easy to reproduce the situation (including Keys). Even better is to use a site like dbfiddle.uk. Second, you are joining the same table twice, deliberate? – Lennart Dec 27 '20 at 10:10
  • @Lennart Hi, yes I already edited with link. Second, yes, as I described it before, not twice, just I need data from masterdata_hyung_event from hyung through masterdata_hyung_description. Tried useing alias but didn't work, still same error. Thanks – Fahmi Dec 27 '20 at 15:10
0

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.

2
  • Wow, I learn something new today. Didn't know that "," join is an old style. I've read somewhere on SO posted last year, use that method tho. Thank you for your help!. Also, can you help me with another problem I've got? I'm so sorry, college didn't teach me anything something related to this so I'm struggling alot when I've got a project. Thank you. – Fahmi Dec 29 '20 at 5:07
  • Post another question, if I cant answer perhaps someone else can – Lennart Dec 29 '20 at 6:43
0

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

1
  • 1
    You should avoid mixing "," joins with ANSI joins. It's too long to fit in a comment, so I'll add an answer. – Lennart Dec 27 '20 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.