通过同一个表中的不同ID连接两行

I would like to join two rows by different ids from a same table.

我想从同一个表中通过不同的id连接两行。

Fruit table:
name:    from_id:   to_id:
Apple    Spain      Italy

Country table:
id:      packaging_type:    packaging_quantity:
Italy    wood box           12
Spain    paper box          18

And I would like to get this table:

我想得到这张桌子：

Spain paper box 18 Italy wood box 12

And I tried something like this:

我尝试过这样的事情：

SELECT a.packaging_type, a.packaging_quantity, b.packaging_type, b.packaging_quantity 
FROM fruit
    inner join country a on a.country.id = fruit.from_id
    inner join country b on b.country.id = fruit.to_id
WHERE fruit.name = 'Apple';

But I got an error:

但是我收到了一个错误：

ORA-00904: "A"."COUNTRY"."ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 2 Column: 12

3 个解决方案

#1

You are setting an alias to the county table in your INNER JOIN. You don't need to repeat the table name after the alias a or s. Just use a.id instead of a.county.id.

您正在INNER JOIN中为县表设置别名。您不需要在别名a或s后重复表名。只需使用a.id而不是a.county.id。

SELECT a.packaging_type, a.packaging_quantity, b.packaging_type, b.packaging_quantity 
FROM fruit
INNER JOIN country a ON a.id = fruit.from_id
INNER JOIN country s ON b.id = fruit.to_id
WHERE fruit.NAME = 'Apple';

Additionally, you can use an alias for the fruit table if you want to be consistent.

此外，如果要保持一致，可以使用水果表的别名。

SELECT a.packaging_type, a.packaging_quantity, b.packaging_type, b.packaging_quantity 
FROM fruit f
INNER JOIN country a ON a.id = f.from_id
INNER JOIN country s ON b.id = f.to_id
WHERE f.NAME = 'Apple';

#2

You just need to use a.id, not a.country.id.

你只需要使用a.id，而不是a.country.id。

So, your query will be:

那么，您的查询将是：

SELECT a.packaging_type, a.packaging_quantity, b.packaging_type, b.packaging_quantity 
FROM fruit
    inner join country a on a.id = fruit.from_id
    inner join country s on b.id = fruit.to_id
WHERE fruit.name = 'Apple';

#3

a.country.id should be a.id

a.country.id应该是a.id

b.country.id should be b.id

b.country.id应该是b.id.

#1