compare 2 db tables

Mike1963 · 12-12-2012, 02:38 AM

hi everyone, i am trying to compare 2 tables and return the difference, i can join the tables (no problem there), but can`t get the difference in the 2. to clarify my problem, table1 has company_id as does table2 but table 2 does not have ALL the company_id`s as table 1 has, now i am wanting to find out the company_id`s that do not match. this is what i am currently trying, i am not getting any errors, all i am getting is the company_id`s that DO match.

Code:

$query=mysql_query("SELECT * from company as c1
INNER JOIN 
messages as c2
on c1.company_id=c2.company_id where c1.company_id != ''") or die (mysql_error());

Old Pedant · 12-12-2012, 04:15 AM

So what is it you *DO* want to get back?

EXPLICITLY!

Just the IDs there are in company but not in messages? Or the IDs and other fields?

Or do you want (EXAMPLE onyl!) thie IDs, the company name, and the count of matching messages?

OR OR OR...???

You need to spell out EXACTLY which fields you want from which table under what circumstance.

Mike1963 · 12-12-2012, 04:34 AM

all i`m wanting is the company_id`s in the company table that dont match the messages table

BubikolRamios · 12-12-2012, 08:11 AM

if you ment:

Code:

c1.id      c2.id
1             1
2

To find 2 as result:

Code:

SELECT c1.company_id 
from company as c1
LEFT JOIN  messages as c2
on c1.company_id=c2.company_id 
where c2.company_id is null

Mike1963 · 12-12-2012, 03:31 PM

when i use c1.company_id or the * i get either ALL the id`s from the company table or ALL the id`s that do match. this is using either the LEFT JOIN or RIGHT JOIN, doesnt matter which one i use. i have tried ever way using left and right join and nothing is giving the right results

BubikolRamios · 12-12-2012, 03:37 PM

Did you try this:

Code:

where c2.company_id is null

Mike1963 · 12-12-2012, 03:55 PM

when i tried that earlier it wouldnt give any results but just tried it again and now it works!! ty so much, now to figure out how to get the company_name to show up in a drop down. lol always something, eh?

Mike1963 · 12-12-2012, 04:40 PM

got it all figured out including the company_name in the drop down box. TY so very much for your help!

rajdeep01 · 01-01-2013, 08:59 AM

We use AdeptSQL's SQLDiff tool for the database structures, and it's not overly expensive. They also do a tool to compare data within tables.

Old Pedant · 01-02-2013, 02:31 AM

Quote:

Originally Posted by rajdeep01

We use AdeptSQL's SQLDiff tool for the database structures, and it's not overly expensive. They also do a tool to compare data within tables.

This post has NOTHING TO DO with the subject of this thread.

If you keep posting IRRELEVANT comments like this, you are going to get some bad reputation marks.

If you are going to answer in a thread, READ the ENTIRE thread, first.