marcsimm marcsimm - 7 months ago 42
SQL Question

Error Code: 1066. Not unique table/alias: 'File'

I'm using MySQL and I'm trying join columns from two ohter table and execute this query

i'm trying arrange the columns like this


  • Transaction.TransactionID

  • Transaction.TransactionDate

  • Transaction.Amount

  • TransactionDescription.Description

  • File.StartDate

  • File.EndDate

  • File.FileName

    SELECT Transaction.TransactionID, Transaction.TransactionDate, Transaction.Amount, TransactionDescription.Description
    ,File.StartDate, File.EndDate, File.Filename
    FROM Transaction
    INNER JOIN TransactionDescription ON TransactionDescription.Description = TransactionDescription.Description
    INNER JOIN File ON File.StartDate = File.StartDate
    INNER JOIN File ON File.EndDate = File.EndDate
    INNER JOIN File ON File.Filename = File.Filename
    ;
    but when the query is executed i get this error

    Error Code: 1066. Not unique table/alias: 'File'
    Why does the error come up?


Answer

Since you have three File tables in your query, MySQL has no way to know which one you mean when you say File. You'd normally fix that assigning different table aliases. But it feels rather weird that you actually need to join the same table three times. I have the impression you only want it once, as in:

SELECT Transaction.TransactionID, Transaction.TransactionDate, Transaction.Amount, TransactionDescription.Description
,File.StartDate, File.EndDate, File.Filename
FROM Transaction
INNER JOIN TransactionDescription ON  TransactionDescription.Description = TransactionDescription.Description
INNER JOIN File ON  File.StartDate = File.StartDate
    AND File.EndDate = File.EndDate
    AND File.Filename = File.Filename
Comments