SQL: Combining Join and where

Question

I just started today with SQL and have following (probably quite newbie) question:

Given two Data Bases Data1 & Data2 having the same number of rows and an identical first column. I want to get all the columns from Data1, but only the rows that meet a condition involving columns of Data2.

I tried something like

SELECT 
    column2
    column3
    ...

FROM
    Data1

INNER JOIN Data2 ON Data1.column1 = Data2.column1
    WHERE 'condition1 involving columns in Data2',
          'condition2 involving columns in Data2',
          ...                                    
;

This does not give me the column1, though. If I include it in the select statement above it throws an error 'Column reference column1 is ambiguous'.

Any ideas what is going on?

Possible duplicate of [Query error with ambiguous column name in SQL](https://stackoverflow.com/questions/12662954/query-error-with-ambiguous-column-name-in-sql) — Aaron Dietz, Feb 23 '18 at 19:55

score 2 · Accepted Answer · answered Feb 23 '18 at 19:58

SELECT Data1.column1
FROM Data1 INNER JOIN Data2
WHERE Data1.column1 = Data2.column2
AND 'SOME CONDITION IS MET'

The key here is that your select needs to define which database column 1 is being selected from.

WHERE allows you to pair the two databases on their primary key (I assume)

AND allows you to add multiple conditions to your select.

score 2 · Answer 2 · answered Feb 23 '18 at 19:58

The problem is that you are joining two tables with a column with the same name. So in those cases you must prefix the column name with the table name or alias (and it is a good idea to avoid future errors to prefix column names even if there are no repeated names). Data1.column1 in your case.

score 1 · Answer 3 · answered Feb 23 '18 at 19:59

Basically, you should always precede column names with table names they belong to, or - a better option - use a table alias. For example:

select a.column1,
       b.column2,
       b.column3
from table1 a join table2 b on a.id = b.id
where b.some_column = 20

Doing so, there won't be any ambiguity there.

SQL: Combining Join and where

3 Answers3