sheldonlg wrote:
>I am probably missing something very obvious, but after looking and
> looking I just don't see it.
>
> I have the following query:
>
> SELECT DISTINCT A.AMT, A.CONTRACT
> FROM ATABLE A, BTABLE B, CTABLE C
> WHERE
> ( (B.X = '011170' AND B.Y = A.W)
> OR
> (C.M = A.Z AND C.N = A.T)
> )
> AND A.FYEAR = '2008'
> AND A.FPRNO='3'
>
> This yields an empty set.
> However, if I remove the OR part and just have:
>
> SELECT DISTINCT A.AMT, A.CONTRACT
> FROM ATABLE A, BTABLE B
> WHERE
> ( (B.X = '011170' AND B.Y = A.W)
> )
> AND A.FYEAR = '2008'
> AND A.FPRNO='3'
>
> Then it returns data.
>
> Since (B.X = '011170' AND B.Y = A.W) is true and
> (C.M = A.Z AND C.N = A.T) is false, shouldn't the compound statement
> in the parenthesis in the first one, separated by an OR, yields true,
> and so should yield data? It is acting as if (true or false) ===>
> false rather than true.

You don't need brackets around the ANDed comparisons, since and already has
the higher order or precedence.

Also, do not use comma joins, they make it difficult to see what is
happening. Re-write it using explicit JOINs so that the intention becomes
clearer.

I notice that you cross-posted to an oracle group. Is this intended for
MySQL or Oracle?