Results 1 to 7 of 7

Thread: Sql Select...

  1. #1
    Join Date
    Apr 2003
    Posts
    7

    Red face Unanswered: Sql Select...

    TABLE: TOWNS
    =============================
    COUNTY = TOWN
    =============================
    LIMERICK = ARDAGH
    LONGFORD = ARDAGH
    TIPPERARY = BALLINA
    MAYO = BALLINA
    WICKLOW = BALLINACLASH
    TIPPERARY = BALLINACLOUGH
    ROSCOMMOM = ATHLEAGUE
    ROSCOMMON = ATHLONE
    WESTMEATH = ATHLONE
    CARLOW = HACKETSTOWN
    WATERFORD = HALFWAYHOUSE
    MEATH = HAYES
    DUBLIN = HAZELHATCH
    =============================
    ok there's my table

    I just don't know how to do it...

    I just want to select the COUNTY and TOWN where the
    TOWN appears more that once. Kinda like this. Can anyone
    help? what is the SELECT statement?

    SELECT RESULT
    =============================
    COUNTY = TOWN
    =============================
    LIMERICK = ARDAGH
    LONGFORD = ARDAGH
    TIPPERARY = BALLINA
    MAYO = BALLINA
    ROSCOMMON = ATHLONE
    WESTMEATH = ATHLONE
    =============================


    I'm not totally new to SQL but I would like to learn more
    advanced SQL, does anyone know goodf tutorial sites?


    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Sql Select...

    It helps to break the problem down into parts:

    1) Which TOWNs appear more than once.

    Easy:

    SELECT town FROM towns
    GROUP BY town HAVING COUNT(*) > 1;

    SELECT RESULT
    =============================
    TOWN
    =============================
    ARDAGH
    BALLINA
    ATHLONE
    =============================

    2) Get all records where TOWN appears in result of (1):

    SELECT county, town
    FROM towns
    WHERE town IN
    ( SELECT town FROM towns
    GROUP BY town HAVING COUNT(*) > 1
    );

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need a subquery for the towns --
    Code:
    select county, town
      from towns
     where town in
           ( select town 
               from towns
           group by town
             having count(*) > 1 )
    rudy
    http://r937.com/

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    darn, beat me by a minute...

  5. #5
    Join Date
    Apr 2003
    Posts
    7
    Cheers

    I knew how to do

    SELECT town FROM towns GROUP BY town HAVING COUNT(*) > 1

    but my problem was that I not to sure how to do subqueries.

    The book on SQL I have is crap. Sams Teach Yourself in 21 Days. Do you or anyone know any good sites on subqueries?

    Thanks andrewst.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by thomaskeegan
    Do you or anyone know any good sites on subqueries?
    Thanks andrewst.
    Not really - my SQL studying days are long in the past (pre-WWW). I refer to on-line Oracle manuals for syntax of new/obscure features.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are several good tutorial sites in my SQL Links --

    http://r937.com/links.cfm?links=sql

    sorry, the links are uncategorized, you'll just have to scroll through them

    categorization is on my To Do list but not near the top...


    rudy

Posting Permissions

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