CODEWITHSUNDEEP
mysqlleft-join
DTH
DTH

Reputation: 1133

Select from two tables gives Not unique table/alias

I have 3 tables (user, user_authority_rules_history & authority_rules_history) I want to select values from two of the tables if they match my criterias.

My query looks like this

SELECT 
  user_authority_rules_history.download_date, 
  authority_rules_history.* 
FROM 
    user_authority_rules_history, 
    authority_rules_history 
  LEFT JOIN 
    user_authority_rules_history 
  ON 
    user_authority_rules_history.authority_rule_id = authority_rules_history.id 
  LEFT JOIN 
    user 
  ON 
    user_authority_rules_history.user_id = user.id 
WHERE 
  user.id = 1

Im able make it work, if i leave out user_authority_rules_history.download_date from my query, but then im obviously missing that data. What am i doing wrong here ?

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You have an extra table reference to user_authority_rules_history in the from clause. A simple rule: never use commas in the from clause.

I think this is what you intend:

SELECT user_authority_rules_history.download_date, 
       authority_rules_history.* 
FROM authority_rules_history LEFT JOIN 
     user_authority_rules_history 
     ON user_authority_rules_history.authority_rule_id = authority_rules_history.id LEFT JOIN 
     user 
     ON user_authority_rules_history.user_id = user.id 
WHERE user.id = 1

Note that you are filtering on user.id = 1. This is undoing your left join, so you might as well use inner join on the tables. I am not sure what you really intend, but this should fix your problem.

Upvotes: 1

Related Questions