SQL when joining two tables on the same attribute how do I select it-CodePudding

Two tables both have the attribute "xy" I say TABLE1 JOIN TABLE2 ON 1.xy 2.xy but when SELECTING xy the error "is ambiguous" turns up, because xy exists twice in the new table. How can I delete one, because they have the same content?

CodePudding user response：

It's a bit unclear to me what exactly you want to do. If you want to join the tables by the xy field and avoid the ambiguity error, the most common practice is to use table aliases. Like this:

SELECT ...
FROM table1 t1
JOIN table2 t2 on t1.xy = t2.xy

Note that you can do the same without aliases but to use the table names instead:

SELECT ...
FROM table1
JOIN table2 on table1.xy = table2.xy

The question

How can I delete one, because they have the same content?

confuses me a bit, bit since you are mentioning joining the tables, I presume you don't want to delete anything but just to avoid the ambiguity error.

CodePudding user response：

You should not try do delete the element you selected, the best practice is to specify the columns that you need in the select statement and exclude the ones you don't care about.

To resolve your problem, you just have to specify in the select statement the table you are referencing for the column.

Example:

SELECT table1.xy,table2.xy,table2.other_column ...
FROM table1
JOIN table2 on table1.xy = table2.xy

CodePudding user response：

When joining two tables, you merely ask it to show you a combined data set, so no information will ever be deleted in a joining.

You can ask it to show it like this:

SELECT `table1.xy` AS `xy` FROM table1 JOIN table2 ON table1.xy = table2.xy

This will show 'table1.xy' as the 'xy' column in the data you request.

Edit: You will have to include all other columns manually, though.