MySQL #1054 – Unknown column 'texts.filename' in 'on clause'

October 21, 2022

I have the following structure in MySQL:

events:
----------------------------------------------------
eventid       event_type     etc.
----------------------------------------------------
1             type1 
2             type2 
3             type1
----------------------------------------------------

event_frag_link:
----------------------------------------------------
eid #event_id fid #fragment_id
----------------------------------------------------
1             asccorn_57c
2             asccorn_58c1
1             asc_mil_34c3
----------------------------------------------------

f_and_t (fragments):
----------------------------------------------------
fid #fragment tid #text_id
----------------------------------------------------
asccorn_57c   asccorn
asccorn_58c1  asccorn
asc_mil_34c3  asc_mil
----------------------------------------------------

texts:
----------------------------------------------------
filename #text_id/tid
----------------------------------------------------
asccorn
asc_mil
...
----------------------------------------------------

A text contains many fragments. An event is linked to these fragments. I would like to search for texts linked to events. So for example, when searching for asccorn I want to find events 1 and 2. When searching for asc_mil I want to return event 1, etc.

I’ve been trying different options and codes and I cannot get around

SELECT DISTINCT eventid, event_type, date_display, date_from, date_to, location, done FROM events
    INNER JOIN event_frag_link ON events.eventid = event_frag_link.eid
    INNER JOIN f_and_t ON texts.filename = f_and_t.fid
    WHERE texts.filename LIKE 'asccorn' # this is probably wrong, I suspect

Why am I getting #1054 - Unknown column 'texts.filename' in 'on clause'?

Update

Thank you for pointing out that texts is missing from my query. This revised query:

SELECT DISTINCT eventid, event_type, date_display, date_from, date_to, location, done FROM events
INNER JOIN event_frag_link ON events.eventid = event_frag_link.eid
INNER JOIN f_and_t ON event_frag_link.fid = f_and_t.fid
INNER JOIN texts ON f_and_t.tid = texts.filename
WHERE texts.filename LIKE 'asccorn'

works.

>Solution :

The ON clause expects a table to be selected from which we can define a relationship, the issue with the above query is that you’ve not included the table.

SELECT DISTINCT eventid, event_type, date_display, date_from, date_to, location, done FROM events
INNER JOIN event_frag_link ON events.eventid = event_frag_link.eid
INNER JOIN texts ON THE_TABLE_RELATION // this is what you are missing
INNER JOIN f_and_t ON texts.filename = f_and_t.fid
WHERE texts.filename LIKE 'asccorn'