Your query will work if you replace the last INNER JOIN with a LEFT
OUTER JOIN.
select ....
from table1
inner join table2
on table1.A = table2.A
inner join table3
on table1.B = table3.B
left outer join table4
on table1.C = table4.C
The resulting row will have a null value for any column selected from
table4.
You say that you are "checking" the values in fields A,B,C. Why are you
doing this with an SQL statement? DM2 supports foreign keys which will
perform this check for you "automatically" during insert and update
processing of the table1 rows.
If column (field) C allows nulls and you use a null instead of a zero
value; then foreign keys will be able to perform all of the checking. In
this case; retrievals of column C should be coded as: VALUE(c,0) to
provide the zero value when the column contains a null.
If you have to store the zero value in column C; you can use foreign
keys for columns A and B and use a trigger to validate column C, when
not zero, when inserts or updates are done. This has the advantage of
keeping the table validation code out of the application program(s).
Philip Sherman
Ron wrote:
I've got 3 tables
table1 containing the field A en field B en C
A en C always have a value, C sometimes
C only has to be checked against table 4 when it's not zero
table2 containing the field A as primary key
table3 containing the field B as primary key
table4 containing the field C as primary key
select from
table1
inner join table2
on table1.A = table2.A
inner join table3
on table1.B = table3.B
inner join table4
on table1.C = table4.C
this doesn't werk when C = zero
no row is returned then
how solve this problem?
So, check for C in table 4 when C has a value
don't check for C in table 4 when C = zero
--
Met vriendelijke groet
Ron van der Poel
fa*******@wanadoo.nl
