CODEWITHSUNDEEP
mysql
manxing
manxing

Reputation: 3305

select data from table1 but need table2 as condition in mysql

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.

Upvotes: 1

Views: 2946

Answers (4)

Bohemian
Bohemian

Reputation: 425003

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

Upvotes: 2

Marcelo Diniz
Marcelo Diniz

Reputation: 2499

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

Upvotes: 1

Vinay
Vinay

Reputation: 1064

Try this:

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

Upvotes: 0

Vinnie
Vinnie

Reputation: 3929

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

Upvotes: 0

Related Questions