Posted by jb1 on 06/25/07 18:57

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.

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?


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.


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


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)

Thank you again everyone for all your help and advise! :-)

JB

[Back to original message]