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

时间:2022-11-18 19:19:43

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


3  

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


0  

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


0  

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


3  

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


0  

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


0  

a.country.id should be a.id

a.country.id应该是a.id

b.country.id should be b.id

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