双LEFT JOIN与相同的表

Question

My sql:

SELECT * FROM ex_pair
LEFT JOIN ex_currency as client
ON client_curr = currency_id
LEFT JOIN ex_currency as company
ON co_curr = currency_id

I need to get data for two currency_id but I have an error

ambiguous column name: 'currency_id'

Is there any way to do it right or i need to use two queries?

Answer 1

You need to include your alias in your join, like this:

SELECT *
FROM ex_pair
LEFT JOIN ex_currency AS client
ON client_curr = client.currency_id
LEFT JOIN ex_currency as company
ON co_curr = company.currency_id

You may also want to do something other than select * as you will have two tables with the same columns - something like

SELECT pair.*, company.currency_id AS company_currency_id, client.currency_id as client_currency_id, [...]
FROM ex_pair AS pair
[...]

This way when you explicitly declare the columns you intend to use from ex_currency with an alias, you can know on the other end more easily which are client and which are company. You will need to do this for each column in the currency table that you want in your result, though that can be done if you have your table structure in your code easily enough by looping over the list of columns and appending the alias.

$array = [
    1=> "currency_id",
    2=> "currency_name"
];

$columns = ""
foreach($array as $column){
    $columns.= "company.".$column." AS company_".$column;
    $columns.= ",client.".$column." AS client_".$column.",";
}

$columns = rtrim($columns,','); 

Gives you

company.currency_id AS company_currency_id,client.currency_id AS client_currency_id,company.currency_name AS company_currency_name,client.currency_name AS client_currency_name

Add that after your SELECT pair.* and you get your columns from the currency table, aliased so you know which is which.