asked on

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!

Swafnil

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!

Roman Gherman

SELECT t.town + ' ' + lga.lganame as lgaFullName
FROM
town as t
inner join
lga
on t.townid = lga.townid

RGBDart

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

RGBDart

doh! what a speed =)))

tobzzz

ASKER

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!

Swafnil

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.

tobzzz

ASKER

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.

Swafnil

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

tobzzz

ASKER

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?

Swafnil

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

tobzzz

ASKER

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!

ASKER CERTIFIED SOLUTION

Swafnil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tobzzz

ASKER

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!

Swafnil

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?

tobzzz

ASKER

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!