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.

>I am probably missing something very obvious, but after looking and
>looking I just don't see it.

You not only removed the OR, you also removed the join to table C.
A join with an empty table C won't return any data regardless of
the WHERE clause.

I have to wonder if you really mean 'OR' (as distinguished from
'AND'). Assuming there is one rows from the join of A and B that
satisfies:

B.X = '011170' and B.Y = A.W and A.FYEAR = '2008' and A.FPRNO='3'

and there are one million rows in table C, regardless of contents,
you're going to get one million rows returned.

>
>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.

If table C is empty, there are no records in the join, so the WHERE
clause is never evaluated.

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?

On Thu, 29 May 2008 20:50:34 +0200, sheldonlg <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.

Implicit joins are just evil. If you write it out usign explicit joins,
your problem becomes clear. 'comma'-joins should be avoided at all costs..
--
Rik Wasmus
....spamrun finished

Gordon Burditt wrote:

> If table C is empty, there are no records in the join, so the WHERE
> clause is never evaluated.
>

That is the answer.

Rik Wasmus wrote:
> On Thu, 29 May 2008 20:50:34 +0200, sheldonlg <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.
>
> Implicit joins are just evil. If you write it out usign explicit joins,
> your problem becomes clear. 'comma'-joins should be avoided at all costs.

Thanks. I will do that.