Hello,
Delphi 2010, EDB 2.04 2

Maybe it's a silly query.

I have two tables.
One table for testing with 1 Record called Zahlen. Z1,Z2,Z3,Z4,Z5,Z6 (Values: 3,8,13,16,35,45).
One table for testing with 1 Record called Test. z1,z2,z3,z4,z5z,z6 (Values: 3,8,13,16,35,45).

When I try in the EDB_Manager:
SELECT Z1,Z2,Z3,Z4,Z5,Z6 From Zahlen L, Test C
where
(IF(L.Z1 IN (C.Z1,C.Z2,C.Z3,C.Z4), 1,0) +
IF(L.Z2 IN (C.Z1,C.Z2,C.Z3,C.Z4), 1,0) +
IF(L.Z3 IN (C.Z1,C.Z2,C.Z3,C.Z4), 1,0) +
IF(L.Z4 IN (C.Z1,C.Z2,C.Z3,C.Z4), 1,0) +
IF(L.Z5 IN (C.Z1,C.Z2,C.Z3,C.Z4), 1,0) +
IF(L.Z6 IN (C.Z1,C.Z2,C.Z3,C.Z4), 1,0)) = 2  ==> works(1 Row)
                                                          
when I change from 2 to 3 then 0 Rows
when I change from 2 to 4 then it works like expected!?

What do I wrong?

TIA
Dieter Nagy

Dieter


Either my brain has switched off for the evening, you've posted the values wrongly, or your query can only ever add to 4. Therefore, with the test set to = 1, = 2, or =3 you should get zero rows. Only when set to = 4 should you get one row.

Roy Lambert [Team Elevate]

Roy,
as always you are right!
My brain has switched off.....

Dieter




Roy Lambert wrote:

Dieter


Either my brain has switched off for the evening, you've posted the values wrongly, or your query can only ever add to 4. Therefore, with the test set to = 1, = 2, or =3 you should get zero rows. Only when set to = 4 should you get one row.

Roy Lambert [Team Elevate]