Joining the same table twice in MYSQL

Question

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

Name the columns in the select rather than * and prepend them with table name or alias and alias the columns. — P.Salmon, May 31 '20 at 07:54

score 1 · Accepted Answer · answered May 31 '20 at 07:55

1

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 ...

answered May 31 '20 at 07:55

Algef Almocera

759
1
6
9

Thanks; do I need to list all the columns (over 60) or can I SELECT * and then rename with Aliases? – Matthew Barraud May 31 '20 at 08:13
1

Best practice is to list down the name of columns even though, yes, it is tedious. You may also consider what you return based on the components you are building or the components who are using the data. You might realize that some of them don't actually need all those 60+ columns. Maybe 12 columns for ComponentOne or 8 for ComponentTwo, but not all 60+ at the same time. Another way to do it is to also create a VIEW which can then be reused. Just be careful of using too much views as they may also present performance concerns. – Algef Almocera May 31 '20 at 09:34
Yes, I found once I started listing the columns how many I didn't actually need for the query! :-) – Matthew Barraud May 31 '20 at 10:03
There ya go. Glad this helped! :) – Algef Almocera May 31 '20 at 12:34

Joining the same table twice in MYSQL

1 Answers1