[thomaskeegan]

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

[andrewst]

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

[r937]

you need a subquery for the towns --

select county, town
  from towns
 where town in
       ( select town 
           from towns
       group by town
         having count(*) > 1 )

rudy
http://r937.com/

[r937]

darn, beat me by a minute...

[thomaskeegan]

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.

[andrewst]

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.

[r937]

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