SQL Unknown, although it is definitely located there - SQL Error (1054)

Question

I have a problem with my sql, I am trying to display the user based on their age group, attaching a string along with the primary key, but I only need one value to be displayed

I think this 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 causes this error

SQL Error (1054): Unknown column "users.aid" in the "on" section

What I can not do.

when I remove "by agegroup.aid = users.aid" from line 5

it displays entries but for every help

I am confused by the way it recognizes a column without an inner join (indicating a join), but when I make a whole inner join of an agegroup table, it merges into a thin one, but with all the records

any ideas? thanks

Answer 1

isue , . :

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

, ON agegroup.aid = users.aid users , fray.

:

compresults → → →

Answer 2

, users.aid , .

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

, . :

competitions -> users -> agegroup

:

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

.

, .