Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

join without duplicates

654000Aug 7 2008 — edited Aug 7 2008
hello,
i'm quite new to oracle and dbms in general, so please be patient ;)
so here goes:
i have two tables: people and cities
my task: select all people who live in a city that has a 'z' in its name or where the city_id field is empty.
so i thought i'd join the two tables and see what happens:

select people.* from people, cities
where (people.city_id = cities.city_id and cities.city_name like '%z%')
or (people.city_id is null);

the problem here is that there are duplicate records for the people who have no city_id. how can i change the where-clause in order to avoid this?
i was told not to use distinct, union, intersect.

greets and thx!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 4 2008
Added on Aug 7 2008
13 comments
1,020 views