On 03/01/2017 12:15 AM, Johann Spies wrote:
> On 28 February 2017 at 17:06, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>
> I have not worked through all this but at first glance I suspect:
>
> select distinct b.* from b ...
>
> is distinct from ...
>
> constitutes a double negative.
>
> What happens if you eliminate the first distinct?
>
>
>
> Thanks Adrian,
>
> The dynamics of the data has changed because of data updates so an exact
> comparison is not possible.
>
> Other tests now confirm that the 28 records are identical in both tables.
> The results then become more confusing:
>
> If I remove the first distinct
> and use "is distinct from"
>
> I get 756 rows
>
> and when I use "is not distinct from"
>
> I get 28.
>
> In the first (756) case when I use "group by" the result of the first
> query is exactly the same as the second one.
To be clear you are looking for records in citation that are different
from citationbackup over a subset(Are there more fields?) of 8 fields,
correct?
What do those 8 fields represent?
Is citationbackup really a backup of citation?
Is there a Primary Key on either/both tables?
What are you grouping by?
Where I am going with this, is that it is not clear to me how you are
matching the two sets of records to determine whether they are different
or not. Your result that yields 756 rows indicates that the comparison
is not an apples to apples comparison, but a comparison of two
'shuffled' sets. Adding the group by seems to sort that out. So some
idea of what constitutes a difference and how you determine which
records from each table you want to match would be helpful. If you could
show the table schema and some sample data it would be even better.
>
> Regards
> Johann
> --
> Because experiencing your loyal love is better than life itself,
> my lips will praise you. (Psalm 63:3)
--
Adrian Klaver
adrian.klaver@aklaver.com