On Feb 11, 2008 7:27 PM, James Eaton <jj@stripped> wrote:
> 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?
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