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.

Answer 2

You need a different alias for the table each time you use it.

SELECT B.BlankTypeCode, B.BlankCode, A1.Amount, A1.Type, P1.PurchaseDate, P1.DatePaid
  FROM Blank AS B
  JOIN Ticket    AS T  ON B.BlankCode = T.Blank_BlankCode
  JOIN MCO_Blank AS M  ON B.BlankCode = M.Blank_BlankCode
  JOIN Purchase  AS P1 ON T.PurchaseID = P1.PurchaseID
  JOIN Purchase  AS P2 ON M.PurchaseID = P2.PurchaseID
  JOIN Payment   AS A1 ON T.PurchaseID = A1.PurchaseID
  JOIN Payment   AS A2 ON M.PurchaseID = A2.PurchaseID
 WHERE A1.Status = "Paid"
   AND A2.Status = "Paid"

You'll need to sort out which versions of the Purchase and Payment tables the selected columns come from, and also what should be in the WHERE clause really.

Answer 3

You can't join a table more than once using the same name, so either join it with an alias like inner join purchase p1 on... or use both of the join predicates together like inner join purchase ON first predicate AND second predicate

Answer 4

SELECT bl.BlankTypeCode ,bl.BlankCode ,pymt.Amount ,pymt.Type ,purc.PurchaseDate ,pymt.DatePaid FROM Blank bl INNER JOIN Ticket tk ON bl.BlankCode = tk.Blank_BlankCode INNER JOIN MCO_Blank mco_bl ON bl.BlankCode = mco_bl.Blank_BlankCode INNER JOIN Purchase purc ON tk.PurchaseID = purc.PurchaseID AND mco_bl.PurchaseID = purc.PurchaseID INNER JOIN Payment pymt ON tk.PurchaseID = pymt.PurchaseID AND mco_bl.PurchaseID = pymt.PurchaseID WHERE pymt.Status = "Paid";