By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,969 Members | 1,705 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,969 IT Pros & Developers. It's quick & easy.

***Sql Query problem

P: n/a
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

Dec 26 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
Dec 26 '05 #2

P: n/a
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/
Dec 26 '05 #3

P: n/a
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
Dec 26 '05 #4

P: n/a
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/

Dec 26 '05 #5

P: n/a
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
Dec 26 '05 #6

P: n/a
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
Dec 27 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.