SELECT statement, return one column of info from two fields by joining tables

Hi experts,

I have two tables:
1. town [fields: townid, town, areaid]
2. lga (local government area) [fields: id, townid, lganame]

Town tbl gives a full list of towns, LGA tbl gives a breakdown of suburbs within the towns.

I need to write an SQL SELECT statement (Server 2008) that will bring back one column of information with a full list of towns and lgas for that area. The two tbl's are related on the townid field.

Previously I was only looking up town names with this statment:
SELECT town FROM town WHERE areaid = 8 ORDER BY town

How do I do this?

Many thanks!
LVL 11
tobzzzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SwafnilCommented:


This will give you one row for each town with a list of LGAs behind the dots:
SELECT townname + ':' + suburbs FROM 
(
SELECT DISTINCT
town.town townname,
suburbs = substring( ( SELECT ', ' + lganame 
FROM lga
WHERE lga.townid = town.townid FOR XML path(''), elements 
),2,500)
FROM town
)

Open in new window


If you just need to join the two tables, use the following query:
SELECT town.town, lganame
FROM 
town
LEFT JOIN 
lga
ON town.townid = lga.townid
ORDER BY town.town, lganame

Open in new window


HTH!
0
Roman GhermanSenior Software EngineerCommented:
SELECT t.town + ' ' + lga.lganame as lgaFullName
FROM
town as t
inner join
lga
on t.townid = lga.townid
0
RGBDartCommented:
select t.town + ' ' + i.lganame as TownsAndIgas
from town t inner join
  Iga i on t.townid = i.townid
where areaid = 8
order by t.town, i.iganame
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

RGBDartCommented:
doh! what a speed =)))
0
tobzzzAuthor Commented:
Thanks to all for replying.
@swafnil: your first query has some errors, I've done my best but I get: Incorrect syntax near ')'
your second query gives 2 columns, not one as I need.
@roma1123 and RGBDart: yours give me TOWN space LGA, what I want is one column with all the towns I used to get (so I'll need a left join, not inner join) but also LGAs in that list too. It doesn't need to identify whether it's an LGA or a TOWN because it's just one big list of areas.

HOpe I'm making sense. Many thanks!
0
SwafnilCommented:
I'll try to fix my first query but I don't have an MSSQL server to test with so this could take some minutes. I'll post an answer as soon as I've figured out the exact syntax.
0
tobzzzAuthor Commented:
I'm not sure the first query is doing what I want though, this is also giving Town:LGA in one row, each row should either be town or LGA, one after the other, in one column. The WHERE part needs to be that areaid = 8, so all towns with an areaid of 8 and all LGAs that are related to those towns that have an areaid of 8.
0
SwafnilCommented:
Try this instead:
SELECT town AS name
FROM town
WHERE areaid = 8
UNION
SELECT lganame AS name
FROM lga WHERE lga.townid IN (SELECT townid FROM town WHERE areaid = 8)

Open in new window

0
tobzzzAuthor Commented:
This looks really good and I thought it had worked ...but... If i do search for towns with areaid = 8 I get a result of 149. There are 812 LGAs for that 149 towns, these are the only ones in the database so far so I know this for sure. So, running your statement should yield 961 results in total but I only get 883. I can't think for the life of me why, as I say - your code looks good to me. Why would there be this anomaly?
0
SwafnilCommented:
I don't exactly know why the UNION statement discards so many rows but it seems as if a missing "ALL" behind the UNION causes the discarded rows. So give this one a try:
SELECT town AS name
FROM town
WHERE areaid = 8
UNION ALL
SELECT lganame AS name
FROM lga WHERE lga.townid IN (SELECT townid FROM town WHERE areaid = 8)

Open in new window


Found a note about the "UNION ALL" here: http://www.devx.com/tips/Tip/31335
0
tobzzzAuthor Commented:
OK I did that and I now get 933 results, so I'm still missing 28 rows somewhere, somehow?

ALSO... I'm doubling the points on this because I've just realised I need a second column and in that second column I need the related town name. If the row is a town not an LGA, then I need both columns to say that towns name, e.g:
Sydney | Sydney
Nth Sydney | Sydney
Manly | Sydney
Auburn | Auburn
Nth Auburn | Auburn
etc...

Thanks!
0
SwafnilCommented:
Try this one:
(SELECT town AS name, town AS town
FROM town
WHERE areaid = 8)
UNION ALL
(SELECT lganame AS name,
town AS town
FROM lga 
JOIN town ON
lga.townid = town.townid
WHERE areaid = 8)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tobzzzAuthor Commented:
Great, that gives both columns. I'm still 28 records short, though - 933 total same as the last statement. Any ideas how to grab those last few records in the select?

Many thanks!
0
SwafnilCommented:
Could it be that some of your LGAs don't have proper townids assigned? The second UNION SELECT searches for LGAs that are assigned to towns that are assigned to areaid 8, so does:
SELECT COUNT(*)
FROM lga 
JOIN town ON
lga.townid = town.townid
WHERE areaid = 8

Open in new window

also give you 883 rows?
0
tobzzzAuthor Commented:
I found a slight difference between the online and offline that accounts for those missing records, sorry about that.

Many thanks for helping me resolve this, I learnt a little along the way!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.