|
View previous topic :: View next topic
|
| Author |
Message |
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
Hi,
My Table has the values below.
| Code: |
Key Origin
----- -------
1 CS
1 CO
2 AB
2 BC
2 CO
3 CO
3 AB
4 CO
4 CS |
My requirement is to get the keys who Origin contains both CO,AB. So for this case I am expecting keys 2,3 to be returned to the output.
I tried to search forum for different usages of predicates ALL and selecting DISTINCT. But I couldn't find much.
Can some one help please?
Regards
Amar
Code'd |
|
| Back to top |
|
 |
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hello,
Will this work out?
| Code: |
SELECT DISTINCT KEY FROM TABLE1 WHERE ORIGIN = 'CO'
INTERSECT
SELECT DISTINCT KEY FROM TABLE1 WHERE ORIGIN = 'AB'; |
|
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
Hi Vasanth,
Thank you. It is working. But if we have more values in the input, we got to keep adding the queries. Would there be any alternate way possible to provide the list of values.
Regards
Amar |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
This appears to be another case of "stone soup" requirements.  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| But if we have more values in the input, we got to keep adding the queries. Would there be any alternate way possible to provide the list of values |
You could always simply write a program that would input the values at run-time and go on from there. . . |
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
Hi,
Programming is also an option. But thinking to keep it as last option, if I can't get any alternate solution at all for this requirement.
Thanks for suggestion.
Regards
Amar |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
If (Key,Origin) is a unique key, you could code it like this. But I doubt it will be faster :
| Code: |
select key from table1 where origin in ('CO','AB')
group by key
having count(*) = 2 |
|
|
| Back to top |
|
|
|
|
