MYSQL Join Question

[xander85]

Hey All,

 

I have the following MYSQL Query:

 

SELECT mlb_answers.qid, mlb_answers.userid, mlb_answers.a1, mlb_answers.a2, mlb_answers.a3, mlb_answers.date, mlb_questions.teamid1, mlb_questions.teamid2, mlb_questions.q1, mlb_questions.q2, mlb_questions.q3, mlb_teams.name AS awayteam, mlb_teams.name AS hometeam
FROM mlb_answers
LEFT JOIN mlb_questions ON mlb_answers.qid = mlb_questions.id
LEFT JOIN mlb_teams ON mlb_questions.teamid1 = mlb_teams.id
LEFT JOIN mlb_teams ON mlb_questions.teamid2 = mlb_teams.id
WHERE mlb_answers.userid = '1'
AND mlb_answers.hdiv = '1'
AND mlb_answers.updated = '0'
AND mlb_answers.date = CURdate( ) 

 

I get the error: #1066 - Not unique table/alias: 'mlb_teams'

 

The query works if I only try to get either the awayteam or hometeam name, but not both. What am I doing wrong?

[xander85]

When I change the query to this:

 

SELECT mlb_answers.qid, mlb_answers.userid, mlb_answers.a1, mlb_answers.a2, mlb_answers.a3, mlb_answers.date, mlb_questions.teamid1, mlb_questions.teamid2, mlb_questions.q1, mlb_questions.q2, mlb_questions.q3, mlb_teams.name AS awayteam, mlb_teams.name AS hometeam
FROM mlb_answers
LEFT JOIN mlb_questions ON mlb_answers.qid = mlb_questions.id
LEFT JOIN mlb_teams ON mlb_questions.teamid1 = mlb_teams.id OR mlb_questions.teamid2 = mlb_teams.id
WHERE mlb_answers.userid = '1'
AND mlb_answers.hdiv = '1'
AND mlb_answers.updated = '0'
AND mlb_answers.date = CURdate( ) 

 

The query works, but it returns awayteam and hometeam as the same name, which is incorrect.

[Barand]

If you join to the same table twice you have to give each join a different alias (so treats it logically as 2 tables)

 

I used t1 and t2

 

SELECT mlb_answers.qid, mlb_answers.userid, mlb_answers.a1, mlb_answers.a2, 
mlb_answers.a3, mlb_answers.date, mlb_questions.teamid1, mlb_questions.teamid2, 
mlb_questions.q1, mlb_questions.q2, mlb_questions.q3, t1.name AS awayteam, 
t2.name AS hometeam
FROM mlb_answers
LEFT JOIN mlb_questions ON mlb_answers.qid = mlb_questions.id
LEFT JOIN mlb_teams t1 ON mlb_questions.teamid1 = t1.id
LEFT JOIN mlb_teams t2 ON mlb_questions.teamid2 = t2.id
WHERE mlb_answers.userid = '1'
AND mlb_answers.hdiv = '1'
AND mlb_answers.updated = '0'
AND mlb_answers.date = CURDATE()