|
View previous topic :: View next topic
|
| Author |
Message |
chiraag
New User
Joined: 19 Apr 2005
Posts: 7
|
|
|
|
I have a column which can have values '001', '002' , '003' etc and I need to select only those records which ONLY have a value '001' and should not contain records with '002' or '003'..
For e.g.
Member 1 '001'
Member 1 '002'
Member 2 '001'
Member 3 '002'
From this I would need only Member 2 to be selected.
Can you help? |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Please review your request. . . .
| Quote: |
| select only those records which ONLY have a value '001' |
"Member 1 '001' " has only '001'  |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
I think chiraag is looking for SQL that will return all rows that have MEMBER where '001' is the value of a column and there are no other MEMBER rows that have a column value of anything other than '001'. I don't know if I explained that well or not.  |
|
| Back to top |
|
|
chiraag
New User
Joined: 19 Apr 2005
Posts: 7
|
|
|
|
| Terry you correctly stated what I needed. Can someone help? |
|
| Back to top |
|
|
PeD
Active User
Joined: 26 Nov 2005
Posts: 459
Location: Belgium
|
|
|
|
Sorry still not clear for me.
Please describe ( for me ) concerned columns : name, type, possible values,.. |
|
| Back to top |
|
|
chiraag
New User
Joined: 19 Apr 2005
Posts: 7
|
|
|
|
Let me try again -
Lets say my table has
Member No Type
-------------- -------
Member1 001
Member1 002
Member2 001
Member3 001
Member3 003
Member4 001
I want a query which will give me the output
Member2
Member4
as they are the only ones which have Type values '001' and nothing else. |
|
| Back to top |
|
|
PeD
Active User
Joined: 26 Nov 2005
Posts: 459
Location: Belgium
|
|
|
|
I am not a great DB2 expert, maybe this one
| Code: |
Select Member from your.table
where Type = '001'
and Member not in
( select Member from your.table
where Type <> '001' )
|
|
|
| Back to top |
|
|
rajesh_mbt
New User
Joined: 27 Mar 2006
Posts: 97
Location: India
|
|
|
|
| PeD wrote: |
I am not a great DB2 expert, maybe this one
| Code: |
Select Member from your.table
where Type = '001'
and Member not in
( select Member from your.table
where Type <> '001' )
|
|
I believe this sql consider about only 001 and not the generic one.
I guess he needs the output which is having only one entry in the table.
Please correct me if my understanding is wrong!!! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Please correct me if my understanding is wrong!!! |
I believe this is true.
Only the rows with '001' are candidates and then only when there are no other values for that same member.
| Quote: |
| as they are the only ones which have Type values '001' and nothing else. |
Please review the complete post this quote was taken from. |
|
| Back to top |
|
|
rajesh_mbt
New User
Joined: 27 Mar 2006
Posts: 97
Location: India
|
|
|
|
| Yeah, you are right. Sorry about this. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Rajesh,
Not to worry - we all misread or misunderstand a post now and again 
d |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
chiraag,
How about trying something like this?
| Code: |
SELECT MEMBER,MAX(TYPE)
FROM YOUR.TABLE
GROUP BY MEMBER
HAVING COUNT(*) = 1
AND MAX(TYPE) = '001';
|
| Quote: |
| we all misread or misunderstand a post now and again |
But we compensate for that by providing multiple solutions |
|
| Back to top |
|
|
sriraj1122
New User
Joined: 18 Dec 2008
Posts: 15
Location: Hyderabad
|
|
|
|
SELECT * FROM TEMP
WHERE NUM = '001'
AND MEMBER NOT IN
(SELECT MEMBER FROM TEMPSK WHERE NUM <> '001')
Just check this qrery and let me know whether you got the desrired result or not chiraag. :-)
Regards,
Sriraj K |
|
| Back to top |
|
|
PeD
Active User
Joined: 26 Nov 2005
Posts: 459
Location: Belgium
|
|
|
|
Why repeat an already given solution?
To add one post?
Because noone reads completely the post?
Don't know!   |
|
| Back to top |
|
|
rajeshwarch
New User
Joined: 24 Mar 2008
Posts: 29
Location: mumbai
|
|
|
|
Hi friend,
try this one may be this will fulfil your requirement
SELECT * FROM YOUR_TABLE
WHERE TYPE NOT IN ('002', '003')
AND MEMBER_NO IN ( SELECT MEMBER_NO FROM YOUR_TABLE
WHERE TYPE = '001')
As per my view the inner query give you the MEMBER_NOs which have TYPE '001' then outer query verifies whether that MEMBER_NOs have other TYPEs('002' or '003').
The result table will have the MEMBER_NOs which have TYPE '001' only. |
|
| Back to top |
|
|
PeD
Active User
Joined: 26 Nov 2005
Posts: 459
Location: Belgium
|
|
|
|
| An what about if TYPE=004 exists ?? |
|
| Back to top |
|
|
|
|
