CODEWITHSUNDEEP
mysql
Jeremy
Jeremy

Reputation: 151

SQL Unknown column, despite it definitely being there - SQL Error (1054)

I am having an issue with my sql, I am trying to display a user based of their age group, attaching the string along with the primary key, but i only want that one value to be shown

I believe it is something like this

SELECT users.gbpfid,
       users.aid,
       compresults.total,
       agegroup.aid AS aid2
FROM   compresults
       INNER JOIN competitions
               ON competitions.cid = compresults.cid
       INNER JOIN agegroup
               ON agegroup.aid = users.aid
       INNER JOIN users
               ON compresults.gbpfid = users.gbpfid
WHERE  competitions.compdate = (SELECT competitions.compdate
                                FROM   competitions
                                       INNER JOIN compresults
                                               ON compresults.cid =
                                                  competitions.cid
                                WHERE  compresults.gbpfid = users.gbpfid
                                ORDER  BY competitions.compdate DESC
                                LIMIT  1)

however this throws up this error

SQL Error (1054): Unknown column 'users.aid' in 'on clause'

Which i cannot make any sense

when i remove the "on agegroup.aid = users.aid" from line 5

it displays the records, but for each aid

enter image description here

I am confused how it recognises the column without the inner join (specifying the connection) but when i do whole inner join of the agegroup table, it joins fine, but with all the records

any ideas? thanks

Upvotes: 4

Views: 124

Answers (2)

zachu
zachu

Reputation: 691

You are trying to join agegroups using users.aid before users table is even joined.

   INNER JOIN competitions
           ON competitions.cid = compresults.cid
   INNER JOIN agegroup
           ON agegroup.aid = users.aid
   INNER JOIN users
           ON compresults.gbpfid = users.gbpfid

As you can see you are truing to access users.aid before is joined. Change order of the joins to: 

competitions -> users -> agegroup

This should work:

   INNER JOIN competitions
           ON competitions.cid = compresults.cid
   INNER JOIN users
           ON compresults.gbpfid = users.gbpfid               
   INNER JOIN agegroup
           ON agegroup.aid = users.aid

You should be able to access users.aid now.

Hope this helps.

Upvotes: 1

Shadow
Shadow

Reputation: 34231

The cause of the isue is that joins are processed from left to right. In your code you have the following order:

   INNER JOIN agegroup
           ON agegroup.aid = users.aid
   INNER JOIN users

So, in the ON agegroup.aid = users.aid you reference the users table before it is joined to the fray.

Change the order of the joins:

compresults -> competitions -> users -> agegroup

Upvotes: 0

Related Questions