Alias with inner joins

Question

I have the following query which has an ambiguous field for 'ID', I know I need to use an alias but seem to be doing something wrong in the second example when I use an alias

SELECT * FROM 01users 

INNER JOIN 01modules ON 01modules.Modules_UserID = 01users.ID 

INNER JOIN 01articles ON 01modules.ID = 01articles.ModuleID

WHERE User =  '$user' AND ID = '$moduleid'

ORDER BY WeekID ASC     

I have followed some online examples and get an error on the second line.

SELECT t1.ID

FROM 01users AS t1

INNER JOIN 01modules ON 01modules.Modules_UserID = t1.ID 

INNER JOIN 01articles ON 01modules.ID = 01articles.ModuleID

WHERE User =  '$user' AND ID = '$moduleid'

ORDER BY WeekID ASC

Any solutions please?

Answer 1

Try below query

SELECT t1.ID
FROM 01users AS t1
INNER JOIN 01modules ON 01modules.Modules_UserID = t1.ID 
INNER JOIN 01articles ON 01modules.ID = 01articles.ModuleID
WHERE User =  '$user' AND t1.ID = '$moduleid'
ORDER BY WeekID ASC

You need to use Alias in where clause as well.

This line

WHERE User =  '$user' AND ID = '$moduleid'

Changed to this

WHERE User =  '$user' AND t1.ID = '$moduleid'