CODEWITHSUNDEEP
mysql
asp.patrickg
asp.patrickg

Reputation: 289

Two different databases in MySQL

I need to select all records on database1 where database1.table1.foreign_key does not exist in database2.table2.some_id.

The other questions here talk about joining both databases through their table's foreign keys but that doesn't work on my case since I'm looking for records where their foreign keys do not exist on the other database's table.

Here's some sample data:

On database1.table1:

id - name - foreign_key
-----------------------

1 - No need - 253

2 - Don't need - 627

3 - Need this - 166

On database2.table2:

id - name - some_id
-------------------

1 - Sample - 627

2 - Another - 253

So with those sample data, after running the query, I expect to get

3 - Need this - 166.

Here is my current solution which doesn't work.

SELECT fish_system_sandbox.receivables.*
FROM fish_system_sandbox.receivables
WHERE fish_system_sandbox.receivables.catch_datum_id NOT IN (SELECT inventory_sandbox2.holdings.catch_id FROM inventory_sandbox2.holdings)

This returns an empty result, and does not produce errors.

Upvotes: 2

Views: 47

Answers (3)

Ormoz
Ormoz

Reputation: 3013

try this:

 select table_1.* from table_1 left join table_2
 on table_1.foreign_key=table_2.some_id
 where table_2.id is null

or alternatively:

select table_1.* from table_1
where foreign_key not in (select some_id from table_2)

these queries gives records from table_1 whose ids are not present in table_2

  • The first query runs faster in MySQL
  • The first query may returns some records more than once if the some_id is not a unique key. To prevent it, you may use select distinct instead of select .

Upvotes: 1

Zafar Malik
Zafar Malik

Reputation: 6844

You can try it-

SELECT t1.* 
FROM db1.table1 t1 
LEFT JOIN db2.table2 t2 ON t1.fk=t2.some_id 
WHERE t2.id IS NULL;

Note: Join fields (t1.fk and t2.some_id) should be indexed for better performance.

Upvotes: 0

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

select * from database1.table where  database1.table.FK not in (
    select database2.table.FK from database2.table  
)

This would return all the rows that are there in the database1 but not in the database2.table based on the foreign key.

Upvotes: 0

Related Questions