OraFAQ Forum: SQL & PL/SQL » hot to select those authors who live in the same zip area

Home » SQL & PL/SQL » SQL & PL/SQL » hot to select those authors who live in the same zip area

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

hot to select those authors who live in the same zip area [message #2291]

Wed, 03 July 2002 13:31

James
Messages: 120
Registered: June 2000

Senior Member

The table can be created as follows.
I want to select those author live in Oakland, CA and in the same zip area.

SELECT au_fname,au_lname,zip
from sameRow
where STATE ='CA'
and CITY= 'Oakland'
??

Attached table creation sql.

drop table sameRow;

create table sameRow(
au_ID NUMBER (5) NOT NULL,
au_fname VARCHAR2(20),
au_lname VARCHAR2(20),
state VARCHAR2(20),
city VARCHAR2(20),
zip VARCHAR2(10)
);

insert into sameRow values (10001,'James','HU','CA','Oakland','12345');

insert into sameRow values (10002,'James1','HU2','CA','Oakland','32345');

insert into sameRow values (10003,'James2','HU3','CA','Oakland','12345');

insert into sameRow values (10004,'James3','HU4','CA','Toronto','416345');

insert into sameRow values (10005,'James4','HU5','CA','Los Angels','52321');

insert into sameRow values (10006,'James5','HU6','CA','Oakland','31234');

commit;

Report message to a moderator

Re: hot to select those authors who live in the same zip area [message #2294 is a reply to message #2291]

Wed, 03 July 2002 14:57

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

What do you mean by the same "zip area"?

You obviously don't mean all authors who live in a particular zip code (where zip = '12345')...

Report message to a moderator

Re: how to select those authors who live in the same zip area [message #2295 is a reply to message #2294]

Wed, 03 July 2002 18:24

James
Messages: 120
Registered: June 2000

Senior Member

Of course not, this is not what I want, I know how to use where zip = '12345', the zip code is only sample.
We do not know the specific zip number in advance.

Report message to a moderator

Re: how to select those authors who live in the same zip area [message #2299 is a reply to message #2294]

Wed, 03 July 2002 22:02

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Hey, you're the one asking for help, not me. If you had explained your requirement clearly enough in the first place, you'd already have an answer.

Report message to a moderator

Re: hot to select those authors who live in the same zip area [message #2302 is a reply to message #2291]

Wed, 03 July 2002 23:08

TAW
Messages: 3
Registered: July 2002

Junior Member

u can use this query.

SELECT a.au_fname, a.au_lname, a.zip
from sameRow a,sameRow b
where a.STATE ='CA'
and a.CITY= 'Oakland'
and a.ZIP = b.ZIP
and a.rowid <> b.rowid;

Report message to a moderator

Re: hot to select those authors who live in the same zip area [message #2308 is a reply to message #2302]

Thu, 04 July 2002 07:18

James
Messages: 120
Registered: June 2000

Senior Member

Thanks a lot, but, ur code only works for the specific sample (ie. there are only 2 authors live in the same zip area), however, if there are more than 2 authors live in the same zip area, it doesn't work, we do not know how many authors live in the same area in advance.
I believe there should be better way.

James

Report message to a moderator

Previous Topic:	Keyword COMPUTE : how to remove -------- and ****** lines from my report????
Next Topic:	Connect By to ensure full connection.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:15:51 CDT 2024