Randy Webb wrote:
> Thomas 'PointedEars' Lahn said the following on 12/26/2005 5:17 PM:
>> Randy Webb wrote:
>>>Thomas 'PointedEars' Lahn said the following on 12/26/2005 3:26 PM:
>>>> However, the condition "value '1' and not value '5'" contains a
>>>> redundancy. What is 1 is always not 5:
>>>>
>>>> SELECT OrderID FROM a WHERE 'Trans ID' = 1 GROUP BY 1
>>>>
>>>> Maybe you mean "value 1 _or_ not 5":
>>> No, it reads "value 1 _and_ not 5".
>>
>> The condition
>>
>> 'Trans ID' = 1 AND 'Trans ID' != 5
>>
>> contains a redundancy because everything that is equal to 1 is always
>> not equal to 5.
>
> Not when the item has multiple Trans ID's which is what the posted data
> shows:
>
> OrderID Trans ID
> 74 1
> 74 4
> 74 5
>
> So, if OrderID # 80 looked like this:
>
> OrderID Trans ID
> 80 1
> 80 4
>
> Then 80 should qualify as it contains 1 but does not have a Trans ID of
> 5. But orderID # 74 shouldn't qualify as it has 1 but also has 5.
SELECT DISTINCT OrderID FROM trans WHERE Trans_ID = 1 AND OrderID
NOT IN (SELECT DISTINCT OrderID FROM trans WHERE Trans_ID = 5);
> > >OrderID 74 has three Trans ID's, 1, 4 and 5.
> > So? Either query I provided will return those ...
>
> Since this is a javascript group, I won't comment on potential SQL
> queries.
Since I have tested successfully this in MySQL Ver 14.12 Distrib 5.0.16:
X-Post & Followup-To comp.databases.mysql

PointedEars