CODEWITHSUNDEEP
mysql
Matthew Barraud
Matthew Barraud

Reputation: 515

Joining the same table twice in MYSQL

I am trying to create a MYSQL query that pulls in data from a range of tables. I have a master bookings table and an invoice table where I am recording invoice id's etc from Stripe.

I am storing two invoices per booking; one a deposit, the second the final balance.

In my admin backend I then display to the admin info on whether the invoice is paid etc so need to pull in data from SQL to show this.

I'm following some previous guidance here What's the best way to join on the same table twice?.

My query is returning data, however when the invoices table is included twice (to give me the deposit and balance invoices) however the column names are identical.

Could someone point me in the right direction? I think I need to somehow rename the columns on the second returned invoice??? Sorry new to anything but basic SQL queries.

This is my SQL

SELECT * FROM bookings 
INNER JOIN voyages ON bookings.booking_voyageID = voyages.voyage_id 
LEFT JOIN emailautomations ON bookings.booking_reference = emailautomations.automation_bookingRef AND emailautomations.automation_sent != 1 
LEFT JOIN invoices ON bookings.booking_stripeDepositInvoice = invoices.invoice_id 
LEFT JOIN invoices inv2 ON bookings.booking_stripeBalanceInvoice = inv2.invoice_id

Thanks to @Algef Almocera's answer I have amended my SQL (and stopped being lazy by using SELECT *, was able to trim loads of columns down to not many!)

SELECT
        bookings.booking_status,
        bookings.booking_reference,
        bookings.booking_stripeCustomerReference,
        bookings.booking_stripeDepositInvoice,
        bookings.booking_stripeBalanceInvoice,
        bookings.booking_totalPaid,
        bookings.booking_voyageID,
        bookings.booking_firstName,
        bookings.booking_lastName,
        bookings.booking_contractName,
        bookings.booking_contractEmail,
        voyages.voyage_id,
        voyages.voyage_name,
        voyages.voyage_startDate,
        depositInvoice.invoice_id AS depositInvoice_id,
        depositInvoice.invoice_status AS depositInvoice_status,
        balanceInvoice.invoice_id AS balanceInvoice_id,
        balanceInvoice.invoice_status AS balanceInvoice_status
      FROM bookings
      INNER JOIN voyages ON bookings.booking_voyageID = voyages.voyage_id
      LEFT JOIN emailautomations ON bookings.booking_reference = emailautomations.automation_bookingRef AND emailautomations.automation_sent != 1
      LEFT JOIN invoices depositInvoice ON bookings.booking_stripeDepositInvoice = depositInvoice.invoice_id
      LEFT JOIN invoices balanceInvoice ON bookings.booking_stripeBalanceInvoice = balanceInvoice.invoice_id

Upvotes: 0

Views: 58

Answers (1)

Algef Almocera
Algef Almocera

Reputation: 819

This, sometimes, couldn't be avoided as keywords might actually often be the same but of different purpose per table. To help with that, you can use aliases. for example:

SELECT 
invoices.column_name AS invoices_column_name,
transactions.column_name AS transactions_column_name
FROM invoices ... 
LEFT JOIN transactions ...

Upvotes: 1

Related Questions