List:General Discussion« Previous MessageNext Message »
From:BJ Swope Date:February 12 2008 1:30am
Subject:Re: Query two different databases for differences
View as plain text  
On Feb 11, 2008 7:27 PM, James Eaton <jj@stripped> wrote:

> SELECT
>  MIN(TableName) as TableName, id, col1, col2, col3, ...
> FROM (
>  SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
>  FROM a
>  UNION ALL
>  SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
>  FROM b
> ) AS tmp
> GROUP BY id, col1, col2, col3, ...
> HAVING COUNT(*) = 1
> ORDER BY ID;
>
>
> This finds common rows.  That doesn't help when the tables have about
> 20,000 rows and most are the same.  How do I invert the query so that I
> can find the 'uncommon' rows?  Second, the primary key ('id' in the
> example) values do not match, so how/where in the query can you specify
> how to match rows from the two tables using other columns?
>
>
SELECT A.message_number
FROM message_table A
LEFT JOIN delivery_table B
ON  A.message_number = B.message_number
WHERE B.message_number is NULL
AND A.deleted = 0;

Will select every message_number that appears in A but not B and A's deleted
field equals 0.

-- 
We are all slave to our own paradigm. -- Joshua Williams

If the letters PhD appear after a person's name, that person will remain
outdoors even after it's started raining. -- Jeff Kay

Thread
Query two different databases for differencesJames Eaton11 Feb
  • Re: Query two different databases for differencesPeter Brawley12 Feb
  • Re: Query two different databases for differencesJames Eaton12 Feb
    • Re: Query two different databases for differencesBJ Swope12 Feb
    • Re: Query two different databases for differencesPeter Brawley12 Feb