Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: Difference between two tables

    Hi,

    I have two tables in the database. The two tables are same schema. One act as source and other as destination. I have to find the difference between two tables. The difference is the records present in the source and not in destination. The table has composite key. I have used INNER JOIN, as shown below

    SELECT DISTINCT source.locations, source.station, source.unit_details,
    FROM production.step_instances source
    INNER JOIN test.step_instances destination ON ( destination.locations!= source.locations AND destination.station != source.station AND destination.unit_details!= source.unit_details);

    The columns locations, station and unit_details are composite keys with different data types. But I found, some issues with the data in the table from the above query. Is this possible to use some mysql function for composite key or other query to find the difference?

    Thanks

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about:
    SELECT DISTINCT source.locations, source.station, source.unit_details,
    FROM production.step_instances source
    WHERE NOT EXISTS (select 1 from test.step_instances destination
    WHERE destination.locations = source.locations AND destination.station = source.station AND destination.unit_details = source.unit_details)


    Dave Nance

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT source.locations
         , source.station
         , source.unit_details
      FROM production.step_instances AS source
    LEFT OUTER
      JOIN test.step_instances AS destination 
        ON destination.locations = source.locations 
       AND destination.station = source.station 
       AND destination.unit_details = source.unit_details
     WHERE destination.locations IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2009
    Posts
    64

Posting Permissions

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