Results 1 to 2 of 2

Thread: Snag

  1. #1
    Join Date
    May 2003

    Question Unanswered: Snag

    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.

    Thank You.

    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%';

    ----------- -------------------- --------------- -------------------- ---------------
    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

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: Snag

    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 <")

    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
    from traveler
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts