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.