Not unique table/alias in SQL-CodePudding

I tried to create a new table that has columns from 2 tables province and district_ward. Both tables have a column NAME, but different values; both tables have a column PROVINCE_ID (same value) and this is the column I tried to match.

Expected output is

PROVINCE_ID / NAME / NAME

Find all NAME from district_ward that match NAME = "Vung Tau" in province, through PROVINCE_ID column.

This is my query, however it throws an error

1066: 'not unique table/alias'

USE db1;

SELECT province.PROVINCE_ID, province.NAME, district_ward.NAME
FROM province as p, district_ward as d
INNER JOIN d ON p.PROVINCE_ID=d.PROVINCE_ID and p.NAME = "Vũng Tàu"
GROUP BY p.PROVINCE_ID

CodePudding user response：

Maybe you should try this one:

USE db1;

SELECT province.PROVINCE_ID, province.NAME, district_ward.NAME

FROM province as p

INNER JOIN district_ward as d USING (PROVINCE_ID)

WHERE p.NAME = "Vũng Tàu"

GROUP BY p.PROVINCE_ID;

CodePudding user response：

Do not use "comma JOIN"

FROM  province as p, district_ward as d
INNER JOIN  d  ON ...

Instead, do

FROM  province as p
INNER JOIN district_ward as d  ON ...

(Optionally use USING instead of ON -- But that's not where the problem is.)