|
|
Posted by jb1 on 06/12/07 20:23
On 12 Jun, 16:24, undercups <d...@woodace.co.uk> 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
> SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a LEFT JOIN
> table2 b on a.id = b.id
> WHERE b.col4 IS NULL
> UNION ALL
> SELECT b.id, b.col1, b.col2, b.col3, a.col4 FROM table2 b LEFT JOIN
> table1 a on b.id = a.id
> WHERE a.col4 IS NULL
>
That's great! Thank you. I'll have a look into this.
Do you have any ideas how I can add the above mentioned STATUS column
after each returned row? eg. If row is different in tbl_B from tbl_A
then add 'M' at the end of the row, as has Master data. something
like.. ("col1.value","col2.value","col3.value","M")
Thanks again :-)
> Note that the col1, col2, col3 and col4 in each of the queries must be
> of the same datatypes or can be converted to the same datatypes e.g.
> VARCHAR(20) is the same as CHAR(5). The where clauses can be more
> specific and varied than I have shown but the important part is the
> test for IS NULL in the last 2 queries as this is the way to find rows
> in one table and that are not in another.
>
> Hope this helps
Navigation:
[Reply to this message]
|