Posted by Ed Murphy on 06/26/07 16:12

jb1 wrote:

> On 13 Jun, 06:25, Ed Murphy <emurph...@socal.rr.com> wrote:
>> undercups wrote:
>>> The answer is to write 3 queries, 1 for each situation you describe
>>> and link the output using the UNION ALL statement
>>> Something like
>>> SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a JOIN table2
>>> b on a.id = b.id
>>> WHERE a.col1 <> b.col1
>>> UNION ALL
>> (etc.)
>>
>> The problem is that, if the tables don't have primary keys, then they
>> may not have a column like 'id'. (Or maybe they do, and it just isn't
>> defined as a primary key - in which case, why not?)
>
> Well there are about 5 or 6 cols in each table and I need to check
> each one to see if anything has changed. There is 1 column in each
> that is kind of like the ID you are talking about though it is not set
> to Primary Key.

Like I said, why isn't it? Is there a good reason not to set it
to Primary Key now?

> Anyway, this is what I have for the 1st query to find Modified rows in
> TableMaster...
>
> SELECT a.id,
> a.col2, a.col3, a.col4, a.col5,
> b.id, b.col2, b.col3, b.col4, b.col5
> 'M' AS status
>
> FROM TableMaster a JOIN TableSlave b on a.id = b.id
>
> WHERE (a.col2 <> b.col2) OR (a.col3 <> b.col3) OR (a.col4 <> b.col4)
> OR (a.col5 <> b.col5)
> UNION ALL
>
> Now does the UNION ALL command at the end imply that another query
> will follow and that the results from this query and the other query
> be joined?

Yes. ALL means to include duplicates, which is unnecessary in this
case (the first section of the query will contribute rows with
status = M, the second will contribute rows with status = A, etc.).

> Will the above view query return what I'm looking for? - rows in
> TableMaster that are different in TableSlave? so tha later I can
> update TableSlave with these new modified row.

Yes, assuming that 'id' values are not changed or duplicated.

> How could I continue and query Rows that are present in TableMaster
> but not in TableSlave? - Status 'A' (add)

For parallelism with the part before UNION ALL:

select a.id, a.col2, a.col3, a.col4, a.col5,
b.id, b.col2, b.col3, b.col4, b.col5
'A' AS status
from TableMaster a
left join TableSlave b on a.id = b.id
where b.id is null

> Finally, I would like to put a 3rd query in for TableMaster and
> TableGrandMaster, which tells me which rows are present in
> TableGrandMaster but missing in TableMaster - Status 'D' (delete)

select a.id, a.col2, a.col3, a.col4, a.col5,
b.id, b.col2, b.col3, b.col4, b.col5
'D' AS status
from TableSlave b
left join TableMaster a on a.id = b.id
where a.id is null

There's also RIGHT JOIN, but I avoid it because it's confusing, and
one of the main packages I work on doesn't allow it anyway.

[Back to original message]