双LEFT JOIN与相同的表

我的sql:</ p>

  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
</ code> </ pre>

我需要获取两个currency_id的数据,但我有错误< / p>


模糊列名:'currency_id'</ p>
</ blockquote>

有没有办法正确或我需要 使用两个查询?</ p>
</ div>

展开原文

原文

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?

dtp87205
dtp87205 在加入时使用别名来表示别名是一个好习惯,并且在每个表中使用该别名引用每个表,即使列不是模糊的。它避免了这样的错误,并使那些可能不熟悉表结构的人更容易阅读。例如,在您的查询中,currency_id或client_curr可能位于连接的任一表中,但常识确实说currency_id位于ex_currency表中。
2 年多之前 回复
dra11767
dra11767 您需要限定所需表的列名,因此您将拥有(tableName).currencycode,tableName是您需要货币代码的表。
2 年多之前 回复
duanmao2774
duanmao2774 发布您的架构。
2 年多之前 回复

2个回答

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.

duanmei1894
duanmei1894 无论你的ex_currency表中有哪些列,名称都在$ array中
2 年多之前 回复



您可以使用您为表格提供的别名:</ p>

  SELECT client  .currency_id as client_currency,company.currency_id as company_currency 
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
</ code> </ pre>
< / DIV>

展开原文

原文

you can use the alias that you give to the tables:

SELECT client.currency_id as client_currency, company.currency_id as company_currency
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

dongmei3869
dongmei3869 对列进行别名是一个好主意,这会排除ex_pair中的列,只显示货币表中的ID字段,如果这是他们想要的全部,那么SELECT client_curr作为client_currency,co_curr作为company_currency FROM ex_pair更好,因为它只需要 一张桌子。
2 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐