Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    May 2011
    Posts
    15
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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());

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    May 2011
    Posts
    15
    Thanks
    4
    Thanked 0 Times in 0 Posts
    all i`m wanting is the company_id`s in the company table that dont match the messages table

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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
    Last edited by BubikolRamios; 12-12-2012 at 08:16 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #5
    New Coder
    Join Date
    May 2011
    Posts
    15
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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

  • #6
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Did you try this:
    Code:
    where c2.company_id is null
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #7
    New Coder
    Join Date
    May 2011
    Posts
    15
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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?

  • #8
    New Coder
    Join Date
    May 2011
    Posts
    15
    Thanks
    4
    Thanked 0 Times in 0 Posts
    got it all figured out including the company_name in the drop down box. TY so very much for your help!

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Quote Originally Posted by rajdeep01 View Post
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •