CODEWITHSUNDEEP
sqlinner-joinalias
Stacey
Stacey

Reputation: 23

Alias with inner joins

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?

Upvotes: 1

Views: 1327

Answers (3)

Stacey
Stacey

Reputation: 23

Here is the solution:

SELECT u.User, m.ID, a.Title, a.Topic, a.Text, a.WeekID, a.DatePosted, a.DateDue, a.TimePublished, a.File1, a.ID AS ArticleID
FROM 01users as u 
INNER JOIN 01modules AS m ON m.Modules_UserID = u.ID 
INNER JOIN 01articles AS a ON m.ID = a.ModuleID
WHERE User =  '$user' AND m.ID = '$moduleid'
ORDER BY WeekID ASC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Learn to use table aliases!

SELECT *
FROM 01users u INNER JOIN
     01modules m
     ON m.Modules_UserID = u.ID INNER JOIN
     01articles a
     ON m.ID = 01artacles.ModuleID
WHERE u.User =  '$user' AND m.ID = '$moduleid'
ORDER BY ?.WeekID ASC

You should qualify all column references, including WeekId -- that is what the ? is for.

You should also list out the columns that you want explicitly rather than using select *.

Upvotes: 0

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

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'

Upvotes: 1

Related Questions