Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wibni
Starting Member
31 Posts |
 Posted - 2011-03-04 : 01:40:02
|
Hello,I'm trying to join 2 tables together.Not sure whether a join is the proper way of doing it actually.My querry returns the correct results but it also returns to many records which are not needed.I'm basically looking for a cleaner solution. Any help much appreciated.My tables:GLASV:IDSEG | SEGVAL | DESC1 | 10 | Tanzania1 | 20 | Chile3 | GGM | Geita3 | SAN | SantiagoGLAMF:Country | City10 | GGM20 | SANMy result should look like:10 | GGM | Tanzania | Geita20 | SAN | Chile | SantiagoMy SQL query:SELECT GLASV.idseg,GLAMF.ACSEGVAL01 CountryNumber, GLAMF.ACSEGVAL03 as SiteNumber, CASE WHEN Glasv.idseg = '000001' THEN GLASV.SEGVALDESC END AS CountryName, CASE WHEN Glasv.idseg = '000003' THEN GLASV.SEGVALDESC END AS SiteNameFROM GLAMF INNER JOIN GLASV ON GLAMF.ACSEGVAL01 = GLASV.SEGVAL or GLAMF.ACSEGVAL03 = GLASV.SEGVAL The result of this querry looks like this:10 | GGM | Tanzania | NULL10 | GGM | NULL | Geita20 | SAN | Chile | NULL20 | SAN | NULL | Santiago |
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2011-03-04 : 09:18:43
|
| Will the following do? If not, please provide the relationship between them.DECLARE @GLASV TABLE (IDSEG INT, SEGVAL VARCHAR(100),[DESC] VARCHAR(100))INSERT INTO @GLASV (IDSEG, SEGVAL,[DESC]) VALUES (1,'10','Tanzania')INSERT INTO @GLASV (IDSEG, SEGVAL,[DESC]) VALUES (1,'20','Chile')INSERT INTO @GLASV (IDSEG, SEGVAL,[DESC]) VALUES (3,'GGM','Geita')INSERT INTO @GLASV (IDSEG, SEGVAL,[DESC]) VALUES (3,'SAN','Santiago')DECLARE @GLAMF TABLE (Country INT,City VARCHAR(100))INSERT INTO @GLAMF (Country ,City) VALUES (10,'GGM')INSERT INTO @GLAMF (Country ,City) VALUES (20,'SAN')SELECT SEGVAL,City,[DESC],(SELECT [DESC] FROM @GLASV T WHERE T.SEGVAL = M.City)FROM @GLASV SINNER JOIN @GLAMF M ON CONVERT(VARCHAR(100),M.Country) = S.SEGVAL |
 |
|
|
wibni
Starting Member
31 Posts |
Posted - 2011-03-04 : 09:52:05
|
| Brilliant! Thank you so much.I got it working now. |
|
|
|
|
|
|
|
|
join 2 tables problem