CodingForums.com - compare 2 db tables

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - compare 2 db tables (http://www.codingforums.com/showthread.php?t=284006)

Mike1963

12-12-2012 02:38 AM

compare 2 db tables

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 (Post 1303356)

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.

All times are GMT +1. The time now is 08:28 PM.