Home » SQL & PL/SQL » SQL & PL/SQL » Find rows of data with some matching fields (SQL*PLUS 10.2.0.1.0 XP)
Find rows of data with some matching fields [message #394607] Sat, 28 March 2009 08:29 Go to next message
mustaine85
Messages: 7
Registered: February 2009
Junior Member
Hi,
Is there a way to identify rows that have the same data in some fields in other rows on the same table?

create table rbooking (
code char(1),
room number(1),
bdate date,
something1 char(1),
something2 char(1)
)
/
insert into rbooking
values ('A', '1', '24-MAR-2009', 'N', 'N')
/
insert into rbooking
values ('A', '1', '24-MAR-2009', 'Y', 'Y')
/
insert into rbooking
values ('B', '2', '21-MAR-2009', 'N', 'Y')
/


So, basically I would like a query that would return any rows where code, room number and bdate are the same i.e. the first 2 rows.
I have tried this but it produces duplicate rows
select *
from rbooking a, rbooking b
where a.code = b.code AND a.room = b.room AND a.bdate = b.bdate
/

Thanks for any help
Re: Find rows of data with some matching fields [message #394608 is a reply to message #394607] Sat, 28 March 2009 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
> the first 2 rows.
Rows in a table are like balls in a basket.

Which are the first 2 balls in a basket?
Rows in a table have NO inherent order.



Did you read & follow the bouncing RED ball advice as stated in URL below?
http://www.orafaq.com/forum/f/1/136107/
Did you read & follow Posting Guidelines as contained in URL below?
http://www.orafaq.com/forum/t/88153/0/
Re: Find rows of data with some matching fields [message #394610 is a reply to message #394608] Sat, 28 March 2009 09:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
BlackSwan wrote on Sat, 28 March 2009 15:13
Did you read & follow Posting Guidelines as contained in URL below?
http://www.orafaq.com/forum/t/88153/0/


In what way did the post not comply to the posting guidelines?
Maybe you can tell me why repeating this over and over is NOT to be considered multiposting.
Re: Find rows of data with some matching fields [message #394650 is a reply to message #394607] Sun, 29 March 2009 05:52 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you haven't told oracle to not join rows to themselves.

Normally for a query like this you'd add a
AND a.primary_key_column != b.primary_key_column

clause.

You need a way of uniquely identifying rows and primary key's how you usually do it.
However, since your sample table doesn't have a primary key you can look at using rowid instead
Re: Find rows of data with some matching fields [message #394651 is a reply to message #394607] Sun, 29 March 2009 06:10 Go to previous message
mustaine85
Messages: 7
Registered: February 2009
Junior Member
YES!! Thank you so much, I got it to work using your idea.
I didn't post the whole complete table, I just knocked up a quick example as I wanted to work most of it out for myself Very Happy
Previous Topic: Oracle 9i- Installation
Next Topic: PLS-00103 error
Goto Forum:
  


Current Time: Wed Dec 07 16:44:48 CST 2016

Total time taken to generate the page: 0.08376 seconds