Home » SQL & PL/SQL » SQL & PL/SQL » Need Help w/ Select Statement to retrieve rows that contain the same values.
Need Help w/ Select Statement to retrieve rows that contain the same values. [message #257571] Wed, 08 August 2007 12:13 Go to next message
mccalla
Messages: 4
Registered: August 2007
Junior Member
Hi,

I hope someone can help me out with this one.

I will try to explain clearly what I am trying to retrieve.

I have the following table w/ data values:

Table 1

col1 - col2
1 - 20
1 - 21
1 - 22
2 - 20
3 - 21

I want to retrieve the col1 value(s) which contains col2 values 20, 21, 22. In this case....I want to retrieve 1.

I tried to use the ALL clause but it returns no rows.

select distinct col1
where col2 = ALL(20,21,22)

I want a select statement (no procedure)
Is this possible...seems like a simple request but this really has me stumped.

Thanks in advance,

Annie
Re: Need Help w/ Select Statement to retrieve rows that contain the same values. [message #257574 is a reply to message #257571] Wed, 08 August 2007 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then,
If you want col1 with any of the values
select distinct col1
from mytable
where col2 IN (20,21,22)
/

or if you want col1 with all values
select col1
from mytable
where col2 IN (20,21,22)
group by col1
having count(*) = 3
/

(Assuming there is no duplicate rows)

Regards
Michel

[Updated on: Wed, 08 August 2007 12:18]

Report message to a moderator

Re: Need Help w/ Select Statement to retrieve rows that contain the same values. [message #257581 is a reply to message #257571] Wed, 08 August 2007 12:40 Go to previous messageGo to next message
mccalla
Messages: 4
Registered: August 2007
Junior Member
oops...need to format my code sorry...

Thanks for the suggestion....


suggestion 1:
select distinct col1
from mytable
where col2 IN (20,21,22)


but the in will not work because it will
also retrieve col1 (2) and (3)
and I just want col1 (1).

suggestion2:

select col1
from mytable
where col2 IN (20,21,22)
group by col1
having count(*) = 3


count will not work because it is counting
col1 ...and col1 can have 3 but will not contain
20,21,22

it could have 20,23,24....


thanks.
Re: Need Help w/ Select Statement to retrieve rows that contain the same values. [message #257583 is a reply to message #257581] Wed, 08 August 2007 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
count will not work because it is counting col1 ...and col1 can have 3 but will not contain 20,21,22

Don't you see I restrict the rows to only those that contains the 3 requested values?

(Suggestion 1 works with the assumption given. Your requirements were not clear and an output result is NOT a requirement.)

Regards
Michel

[Updated on: Wed, 08 August 2007 12:48]

Report message to a moderator

Re: Need Help w/ Select Statement to retrieve rows that contain the same values. [message #257584 is a reply to message #257571] Wed, 08 August 2007 12:53 Go to previous messageGo to next message
mccalla
Messages: 4
Registered: August 2007
Junior Member
Hi Michel,

Yes... I did...but it will if my table had the
following values


col1 - col2
1 - 20
1 - 21
1 - 22
2 - 20
3 - 21
3 - 23
3 - 24 



select will bring 1 and 3. no? and I just want 1....
values 20, 21, 22.

To clarify...sorry if I wasn't clear in original request...
I want to retrieve col1 values that have col2 values 20 AND
21 AND 22. (Not Or)

Thanks in advance.



[Updated on: Wed, 08 August 2007 12:57]

Report message to a moderator

Re: Need Help w/ Select Statement to retrieve rows that contain the same values. [message #257586 is a reply to message #257584] Wed, 08 August 2007 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: Need Help w/ Select Statement to retrieve rows that contain the same values. [message #257589 is a reply to message #257586] Wed, 08 August 2007 13:03 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
@Mcalla

Why don't you execute it and see it yourself.
Re: Need Help w/ Select Statement to retrieve rows that contain the same values. [message #257591 is a reply to message #257584] Wed, 08 August 2007 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And my answer is still the same after your update: the second query works.

And if you posted the create table and insert statements, I'd show you.

Regards
Michel
Re: Need Help w/ Select Statement to retrieve rows that contain the same values. [message #257592 is a reply to message #257571] Wed, 08 August 2007 13:12 Go to previous message
mccalla
Messages: 4
Registered: August 2007
Junior Member
Thanks Michel!
It works.... You saved my day.
Previous Topic: Versioning of PL/SQL codes
Next Topic: Pivot and Crosstab Queries
Goto Forum:
  


Current Time: Sat Dec 10 18:15:28 CST 2016

Total time taken to generate the page: 0.05852 seconds