select data from table1 but need table2 as condition in mysql

Question

I want to do:

 select column1
 from db1.table1
 where db1.table1.column2 = db2.table2.column1.

In this case, the error message is:

unknown column: db2.table2.column1

My setting for the database 1 and 2 is correct, just don't know how to write the query in this case.

Just to make sure, both of the tables are in different databases? — Vinay, Jan 20 '12 at 14:51
add table2 in your select part, you need to select the tables before conditioning them. also it seems your using 2 databases? — Moonlight, Jan 20 '12 at 14:52
Are you going to accept one of these answers? Or do none of them work for you? — , Jan 25 '12 at 13:02

Bohemian · Accepted Answer · 2012-01-20T23:43:29.347

2

select t1.column1
from db1.table1 t1
join db2.table2 t2 on t1.column2 = t2.column1

You'll notice that this query uses the more modern, and preferable, join table on condition syntax

edited Jan 20 '12 at 23:43

answered Jan 20 '12 at 14:51

Bohemian

You might want to qualify the `column1` to a table as the name may exist in both. – Karlson Jan 20 '12 at 16:49

score 1 · Answer 2 · edited May 23 '17 at 12:22

1

You need to list db2.table2 in the FROM clause. Assuming you want column1 from db.table1:

select t1.column1
from db1.table1 t1, db2.table2 t2
where t1.column2 = t2.column1

Otherwise you cannot use them in the WHERE clause.

If you prefer using the join ... on syntax (called explicit join), then check Bohemian's answer.

For a discussion about which syntax to pick, see Explicit vs implicit SQL joins

edited May 23 '17 at 12:22

Community

answered Jan 20 '12 at 14:50

Marcelo Diniz

if you have the same columns in table1 and table2 you can use table1.column1 – Moonlight Jan 20 '12 at 14:53

score 0 · Answer 3 · answered Jan 20 '12 at 14:51

0

select column1
from db1.table1, db2.table2
where db1.table1.column2  = db2.table2.column1

answered Jan 20 '12 at 14:51

Vinnie

score 0 · Answer 4 · answered Jan 20 '12 at 14:54

0

Try this:

SELECT
    db1.col1,
    db2.col2
FROM
    db1.tb1 
LEFT JOIN
    db2.tb2 ON db2.tb2.col1 = db1.tb1.col2

answered Jan 20 '12 at 14:54

Vinay

4 Answers4