Joins and subqueries

[SergeS]

Hi all,

I would like to know something about main queries and subqueries.

I've got 3 tables: A, B and C.
The 3 tables have got the same PK, each composed of three attributes.

Table B contains all possible combinations and is the leading table.

The cardinalities are as follows:

A -> B = 1:1
B -> A = 0:N

C -> B = 1:1
B -> C = 0:N

I want to create a list which shows a value from A and a value from C for all rows in B. This is simple. It appears that some combinations between B and C do not exits for A and B and vice versa. When both combinations do exist it is possible that values from A and C are different as well as equal.

Now I want to create a list that only shows the differences between A and C meaning:
combinations of A and B not existing for B and C;
combinations of C and B not existing for B and A;
combinations of A, C and B but different values in A and C (the values should be the same).

When I am adding filters or calculations the first two differences are not shown, or showing duplicate rows.

So I was think, I should use subqueries. How can I use subqueries and create one list with the PK from B and the missing or existing corresponding values from A and C?

Thanks in advance,

Serge

 

[clemenspoetter]

Hi Serge,

your filter probably looks like this:

 a.value <> c. value

Extend it to

 (a.value <> c. value) or
(a.value is null     and c.value is not null) or
(a.value is not null and c.value is null)

Make sure the tables B, A und B, C are outer joined.

Best,
Clemens