Two different databases in MySQL

Question

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.

SELECT * FROM fish_system_sandbox.receivables WHERE fish_system_sandbox.receivables.catch_datum_id NOT IN (SELECT catch_id FROM inventory_sandbox2.holdings) — asp.patrickg
– asp.patrickg, Commented Jun 29, 2015 at 6:10
Please post your tables columns definitions and some of actual data. Your query seems OK, you may have problems in tables designation — Ormoz
– Ormoz, Commented Jun 29, 2015 at 6:34

Ormoz · Accepted Answer · 2015-06-29 06:29:29Z

1

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 .

edited Jun 29, 2015 at 6:29

answered Jun 29, 2015 at 6:08

Ormoz

3,02111 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Danyal Sandeelo · Accepted Answer · 2015-06-29 06:20:03Z

0

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.

answered Jun 29, 2015 at 6:20

Danyal Sandeelo

12.4k10 gold badges57 silver badges90 bronze badges

Comments

Zafar Malik · Accepted Answer · 2015-06-29 06:28:08Z

0

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.

answered Jun 29, 2015 at 6:28

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

Collectives™ on Stack Overflow

Two different databases in MySQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related