Inner Joining the same table multiple times

Question

So I have received this error: #1066 - Not unique table/alias: 'Purchase' I am trying to do the following: ``` SELECT Blank.BlankTypeCode ,Blank.BlankCode ,Payment.Amount ,Payment.Type ,Purchase.PurchaseDate ,Payment.DatePaid FROM Blank INNER JOIN Ticket ON Blank.BlankCode = Ticket.Blank_BlankCode INNER JOIN MCO_Blank ON Blank.BlankCode = MCO_Blank.Blank_BlankCode INNER JOIN Purchase ON Ticket.PurchaseID = Purchase.PurchaseID INNER JOIN Purchase ON MCO_Blank.PurchaseID = Purchase.PurchaseID INNER JOIN Payment ON Ticket.PurchaseID = Payment.PurchaseID INNER JOIN Payment ON MCO_Blank.PurchaseID = Payment.PurchaseID WHERE Payment.Status = "Paid"; ``` Blank Table consists of: ``` BlankCode, IssueDate, Status, UserID, BlankTypeCode ``` Payment Table consists of: ``` Type, Amount, Status, DueDate, PurchaseID, CurrencyRateDate ``` Purchase Table consists of: ``` PurchaseID, CustomerID, PurchaseDate, TotalFare, TaxAmount, CurrencyType, Purchasecol ``` Ticket Table consists of: ``` Price, Purc…

Accepted Answer

You need to use table aliases. You have mentioned the same table more than once in the `from` clause. The query is something like this:

```
SELECT b.BlankTypeCode, b.BlankCode, pa1.Amount, pa1.Type, p1.PurchaseDate, pa2.DatePaid
FROM Blank b
INNER JOIN Ticket t
ON b.BlankCode = t.Blank_BlankCode
INNER JOIN MCO_Blank mb
ON b.BlankCode = mb.Blank_BlankCode
INNER JOIN Purchase p1
ON  t.PurchaseID = p1.PurchaseID
INNER JOIN Purchase p2
ON mb.PurchaseID = p2.PurchaseID
INNER JOIN Payment pa1
ON t.PurchaseID = pa1.PurchaseID
INNER JOIN Payment pa2
ON mc.PurchaseID = pa2.PurchaseID
WHERE pa1.Status = "Paid";
```

I had to make a guess at which payment and purchase is intended for the aliases. These may not be correct in the `from` and `where` clauses.

Inner Joining the same table multiple times

Solution

Problem