SercioSoydanov SercioSoydanov - 18 days ago 9
MySQL Question

How to prevent ambiguous name on inner join with two references to same table

I have two tables as follows (in mysql):

Table: invoice

# Column Name
1 Id
2 invoice_date
3 invoice_no
4 consigned_to
5 invoiced_to
6 ...


Table: company

# Column Name
1 Id
2 title
3 ...


Both consigned_to and invoiced_to columns on first table are referencing
company.Id
.

What I am trying to achieve is a query with following columns

Column Name Table Name
Id (invoice)
invoice_date (invoice)
invoice_no (invoice)
consigned_to (invoice)
consigned_title (company.title)
invoiced_to (invoice)
invoiced_title (company.title)


I need unique column names for the consigned_title and invoiced_title columns, because I should be able to query those columns with titles from company table.

I managed to join single column like this with an alias:

SELECT invoice.*, company.title as consigned_title
from invoice
INNER JOIN company ON invoice.consigned_to = company.Id


but could not managed to reference the same column from company for joining with the invoice.invoiced_to. Is it even possible?

Answer

You need table aliases:

SELECT i.*, cc.title as consigned_title, ci.title as invoiced_title
FROM invoice i INNER JOIN
     company cc
     ON i.consigned_to = cc.Id INNER JOIN
     company ci
     ON i.invoiced_to = ci.id;
Comments