SercioSoydanov SercioSoydanov - 1 year ago 76
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

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 Source

You need table aliases:

SELECT i.*, cc.title as consigned_title, ci.title as invoiced_title
     company cc
     ON i.consigned_to = cc.Id INNER JOIN
     company ci
     ON i.invoiced_to =;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download