SQL - Testing dB

Question

I have a MySQL database that is not setup correctly. I need a Query that will help me find out where.

Two tables Table1.xyz_ID Table2.xyz_ID

Some entries in Table1.xyz_ID have no corresponding field in Table2.xyz_ID What is the SQL to sort Table2 entries that are in error?

Provide some sample data and expected output into the question. — Abhik Chakraborty
– Abhik Chakraborty, Commented Nov 25, 2014 at 6:30
MySQL workbench and other tools can do schema comparisons and generate sql to synchronize the schemas for you. That being said if it is a one off or infrequent task then you can use information_schema tables/views. — T I
– T I, Commented Nov 25, 2014 at 8:13

drunken_monkey · Accepted Answer · 2014-11-27 12:37:12Z

1

select t1.xyz_ID
from Table1 t1
left join Table2 t2
  on t1.xyz_ID = t2.xyz_ID
where t2.xyz_ID is null

Provides all IDs that are in Table1 but not in Table 2

answered Nov 27, 2014 at 12:37

drunken_monkey

1,8781 gold badge13 silver badges14 bronze badges

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

1 Comment

That looks good. Thank you Sir Monkey. I guess I'll have to break down and learn about 'Joins'