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 |
|
asarak
Starting Member
36 Posts |
 Posted - 2005-10-25 : 01:26:59
|
| Hi guys, I want to know if there is another way to include all data even if pTail field is null and only the pHeader is the same with the first 3 digits from PostalCodeselect r.Suraname, r.Fname, r.JobDesc, r.Address, r.Town, isnull(p.Area,'-'), isnull(p.Region,'-'), r.PostalCodefrom Ranks as rleft outer join Regions as p on p.pHeader=left(r.PostalCode,3) and p.pTail is nullgroup by ...order by ...The tables have the following dataRanksSurname Fname JobDesc Address Town PostalCodeGATES BILL IT STREET 13 NEW YORK 123 45SUTTER HERB IT STREET 14 NEW YORK 123 HUWE MARIE MD STREET 1334 LONDON 441BLOOM DOMINIK DF STREET 432 LONDON 442SOLARI SANTIAGO IT STREET 1245 LONDON 442 00COREL JOHN MD STREET 456 MANCHESTER 857 15and RegionsArea Region pHeader pTailPRIME LONDON 441 NULLPRIME LONDON 442 00SECOND MANCHESTER 857 NULLOUTLAND NEW YORK 123 NULLOUTLAND NEW YORK 123 41OUTLAND NEW YORK 123 42OUTLAND NEW YORK 123 43OUTLAND NEW YORK 123 44OUTLAND NEW YORK 123 45RegardsASARAK |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-25 : 01:33:45
|
| What do you get from that query?MadhivananFailing to plan is Planning to fail |
 |
|
|
asarak
Starting Member
36 Posts |
Posted - 2005-10-25 : 01:51:37
|
| I just want to get all the data from Ranks and the Area and Region from Regions when i check the PostalCode to be in the proper Area.For Example,RanksSurname Fname JobDesc Address Town PostalCodeGATES BILL IT STREET 13 NEW YORK 123 45SUTTER HERB IT STREET 14 NEW YORK 123 HUWE MARIE MD STREET 1334 LONDON 441BLOOM DOMINIK DF STREET 432 LONDON 442SOLARI SANTIAGO IT STREET 1245 LONDON 442 00COREL JOHN MD STREET 456 MANCHESTER 857 15and RegionsArea Region pHeader pTailPRIME1 LONDON 441 NULLPRIME2 LONDON 442 00SECOND MANCHESTER 857 NULLOUTLAND1 NEW YORK 123 NULLOUTLAND2 NEW YORK 123 41OUTLAND3 NEW YORK 123 42OUTLAND4 NEW YORK 123 43OUTLAND5 NEW YORK 123 44OUTLAND6 NEW YORK 123 45Name Surname..Town - Area - Region - PostalCode Bill - Gates - New York - OUTLAND6 - NEW YORK - 123 45 Solari-Santiago-London - PRIME 2 - LONDON - 442 00Do i make it clear?? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-25 : 04:04:37
|
| I asked you what your query returnsMadhivananFailing to plan is Planning to fail |
|
|
|
asarak
Starting Member
36 Posts |
Posted - 2005-10-25 : 07:08:42
|
| this is the output...BLOOM DOMINIK DF STREET 432 LONDON - - 442COREL JOHN MD STREET 456 MANCHESTER SECOND MANCHESTER 857 15GATES BILL IT STREET 13 NEW YORK OUTLAND NEW YORK 123 45HUWE MARIE MD STREET 1334 LONDON PRIME1 LONDON 441SOLARI SANTIAGO IT STREET 1245 LONDON - - 442 00SUTTER HERB IT STREET 14 NEW YORK OUTLAND NEW YORK 123 |
|
|
|
|
|
|
|
|
PostalCodes Splitting