CODEWITHSUNDEEP
mysqlsql
user882670
user882670

Reputation:

Query two databases in the same server

I have two databases in the same server and I need to: 

Select from table1 (in database1) the id field
Where notific1=1

But only the user_id list 

Where in table2 (in database2) the enabled field=1

Being the user_id field in the two tables the same.

I tried:

SELECT table1.id 
    FROM [database1].[dbo].[table1] as users
    INNER JOIN [database2].[dbo].[table2] as subs
    ON users.user_id=subs.user_id
    WHERE users.notific1=1 AND
    WHERE subs.enabled=1

This is throwing the error:

.#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[database1].[dbo].[table1] as users INNER JOIN [database2].[dbo]' at line 2

NOTE: I'm using MySQL

Upvotes: 1

Views: 67

Answers (5)

Lalit Patel
Lalit Patel

Reputation: 108

OR Condition

SELECT u1.*, 
       u2.* 
FROM   db1.table1 u1 
       INNER JOIN db2.table2 u2 
               ON u1.id = u2.id 
WHERE  u1.id IN( '12', '16' ) ** 
       OR ** u2.id = 17

AND Condition

SELECT u1.*, 
       u2.* 
FROM   db1.table1 u1 
       INNER JOIN db2.table2 u2 
               ON u1.id = u2.id 
WHERE  u1.id IN( '12', '16' ) ** 
       AND ** u2.id != ''

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 1549

"where" is written two time..

try below:

SELECT table1.id 
    FROM database1.dbo.table1 as users
    INNER JOIN database2.dbo.table2 as subs
    ON users.user_id=subs.user_id
    WHERE users.notific1=1 AND
 subs.enabled=1

Upvotes: 0

StuartLC
StuartLC

Reputation: 107247

There isn't a dbo schema in MySql as as per SqlServer. Just drop the dbo:

SELECT table1.id 
    FROM database1.table1 as users
    INNER JOIN database2.table2 as subs
    ON users.user_id=subs.user_id
    WHERE users.notific1=1 AND
    WHERE subs.enabled=1

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can not have 2 where as you did ( since you tagged your question with Mysql)

WHERE users.notific1=1 AND
WHERE subs.enabled=1

It should be

WHERE 
users.notific1=1 
AND
subs.enabled=1

Also while JOINING the 2 databases the syntax is

select * from proj1.users as y 
inner join project2.users f on f.email = y.email 
and y.email = '[email protected]'

proj1 and project2 are 2 databases in the same server and users is the tables in those databases.

Upvotes: 2

G one
G one

Reputation: 2729

For mysql, the square brackets [] are not used for object names. Use back ticks instead

SELECT table1.id 
    FROM `database1`.`table1` as users
    INNER JOIN `database2`.`table2` as subs
    ON users.user_id=subs.user_id
    WHERE users.notific1=1 AND
    subs.enabled=1

Upvotes: 0

Related Questions