sql - 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,
PurchaseID,
Blank_BlankCode,
Blank_BlankTypeCode,
TicketType,
Airline_Name

MCO_Blank Table consists of:

Service,
Cost,
Description,
Purchase_PurchaseID,
Blank_BlankCode,
Blank_BlankTypeCode

I am unsure of how I can make this work.

Answer 1

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.