CODEWITHSUNDEEP
mysqldatabasejoinsubquery
JMcK
JMcK

Reputation: 100

Return rows from MYSQL table A where table_A.col_1 = table_b.col_2 without using a left outer join?

In a MySQL database with two tables table_A and table_B I want to return selected row columns from table_A based on comparison with values in table_B. The below erroneous line sums up the idea:

SELECT col_1, col_2, col_3 FROM table_A where table_A.col_1 = table_B.col_2;

I do not want any elements from table_B.

Why I can't use a left outer join: I've tried this with a left outer join as illustrated here(https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/) however the database complains that the column names are ambiguous and changing table column names in the database isn't an option.

Upvotes: 0

Views: 121

Answers (2)

Bohemian
Bohemian

Reputation: 425308

If column names are ambiguous, qualify them, eg

select table_A.col_1, table_A.col_2, table_A.col_3
from table_A
join table_B on table_A.col_1 = table_B.col_2

or for brevity you can assign an aliases to tables:

select a.col_1, a.col_2, a.col_3
from table_A a
join table_B b on a.col_1 = b.col_2

Upvotes: 1

GMB
GMB

Reputation: 222652

I you want rows in table_A whose col1 can be found in table_B(col_2), you can use exists:

select a.col_1, a.col_2, a.col_3 
from table_A a 
where exists (select 1 from table_B b where b.col_2 = a.col_1);

If you want rows that do not exist in table_B, then just change exists to not exists.

Note that I prefix the column names with the (alias of the) table they belong to. This is called qualifying the columns, and is how you avoid the ambiguous column name problem that you seemingly have met when trying to join.

Upvotes: 1

Related Questions