***Sql Query problem

[srinivas]

hi I have the data in sql server table like

OrderID Trans ID
74 1
74 4
74 5
76 1
76 4
76 5
here please observe TransID 1,4,5 is repeating in all the orderID
Field.In my table some of the order's have the value "1" but not have
"5".We need to find the orders id's which are having 1 for that order
id and not having the value 5.Please send me a query for this

Thanks,
Srui

[Thomas 'PointedEars' Lahn]

srinivas wrote:

hi I have the data in sql server table like

OrderID Trans ID
74 1
74 4
74 5
76 1
76 4
76 5

here please observe TransID 1,4,5 is repeating in all the orderID
Field.
Pardon?
In my table some of the order's have the value "1" but not have
"5".We need to find the orders id's which are having 1 for that order
id and not having the value 5.Please send me a query for this

Wrong newsgroup.

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":

SELECT OrderID FROM a WHERE 'Trans ID' != 5 GROUP BY 1
PointedEars

[Randy Webb]

Thomas 'PointedEars' Lahn said the following on 12/26/2005 3:26 PM:

srinivas wrote:

hi I have the data in sql server table like

OrderID Trans ID
74 1
74 4
74 5
76 1
76 4
76 5

here please observe TransID 1,4,5 is repeating in all the orderID
Field.

Pardon?

In my table some of the order's have the value "1" but not have
"5".We need to find the orders id's which are having 1 for that order
id and not having the value 5.Please send me a query for this

Wrong newsgroup.

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

OrderID 74 has three Trans ID's, 1, 4 and 5.

--
Randy
comp.lang.javascript FAQ - http://jibbering.com/faq & newsgroup weekly
Javascript Best Practices - http://www.JavascriptToolbox.com/bestpractices/

[Thomas 'PointedEars' Lahn]

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. So that condition does not really make sense. Instead,
an OR makes more sense, for the query result would contain _additional_
data then (hence the misuse/non-logical use of "and").
OrderID 74 has three Trans ID's, 1, 4 and 5.

So? Either query I provided will return those ...
PointedEars

[Randy Webb]

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.

It is the same scenario as if OrderID was a FlightNum and TransID was
layover sites on a plane flight. If you want all the flights from New
York to Moscow that do not layover in London but do layover in Paris
then its "Yes London AND Not Paris".
So that condition does not really make sense.
Sure it does. Re-read it though.
Instead, an OR makes more sense, for the query result would contain
_additional_ data then (hence the misuse/non-logical use of "and").
The OP is wanting to minimize data, not have additional data.

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.

--
Randy
comp.lang.javascript FAQ - http://jibbering.com/faq & newsgroup weekly
Javascript Best Practices - http://www.JavascriptToolbox.com/bestpractices/

[Thomas 'PointedEars' Lahn]

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

[Guy]

srinivas a écrit :

hi I have the data in sql server table like

OrderID Trans ID
74 1
74 4
74 5
76 1
76 4
76 5
here please observe TransID 1,4,5 is repeating in all the orderID
Field.In my table some of the order's have the value "1" but not have
"5".We need to find the orders id's which are having 1 for that order
id and not having the value 5.Please send me a query for this

Thanks,
Srui

Bonjour,

try :

select A.OrderId from Table A
where A.TransID=1 and A.OrderID not in (Select B.OrderID from Table B
where B.TransID=5 )

(and respect type of data)

GR