I made mistake in the SQL statement below, I could not figure it out. I want to select traveler_no's who come from the same suburb and same state whose surname contains 'a'. I am successful with the latter, but the same suburb and state seem to be a problem.
SQL> select traveler.traveler_no,traveler.traveler_surname,tra veler.traveler_firstname,traveler.suburb,traveler. state
2 from traveler
3 where traveler.suburb = traveler.suburb and traveler.state = traveler.state and
4 traveler.traveler_surname like '%a%';
TRAVELER_NO TRAVELER_SUR TRAVELER_FIRST SUBURB STATE
----------- -------------------- --------------- -------------------- ---------------
8 carrey jim canterbury nsw
3 carpenter jack caulfield victoria
5 anthony marc caulfield victoria
6 swayze patrick carnegie victoria
9 barrymore drew croydon nsw
11 damon matt burwood nsw
I'm not quite sure what you want to see in your output here. Your query is currently saying e.g. "where traveler.suburb = traveler.suburb", which is always true for every person (unless SUBURB is null), because is compares a value with itself.
If you wanted to compare pairs of records it would be more like this:
select t1.traveler_no, t1.traveler_surname, t1.traveler.traveler_firstname, t1.suburb, t1.state,
t2.traveler_no, t2.traveler_surname, t2.traveler.traveler_firstname, t2.suburb, t2.state
from traveler t1, traveler t2
where t1.suburb = t2.suburb
and t1.state = t2.state
and t1.traveler_surname like '%a%'
and t2.traveler_surname like '%a%';
(This would list each pairing twice, e.g. jack,marc and marc,jack unless you put in a condition like "AND t1.name < t2.name")
On the other hand, if you want to group ALL people from the same place together, whether there are 2 or 22, you would just sort the data that way:
select traveler.suburb,traveler.state,traveler.traveler_n o,traveler.traveler_surname,traveler.traveler_firs tname
where traveler.traveler_surname like '%a%'
order by state, suburb;
In SQL Plus you can use the command "BREAK ON state ON suburb" so that each state and suburb are only printed once.