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 |
|
jszulc
Yak Posting Veteran
66 Posts |
 Posted - 2008-09-25 : 18:39:03
|
| I have too tables that look like this:TABLE 1Address City Server State SID7002 Prospect Place Northeast Albuquerque ABQNV001 NM F0201817002 Prospect Place Northeast Albuquerque ABQNV001 NM F0019497002 Prospect Place Northeast Albuquerque ABQNV001 NM N0745297002 Prospect Place Northeast Albuquerque ABQNV001 NM D1467577002 Prospect Place Northeast Albuquerque ABQNV001 NM E0461977002 Prospect Place Northeast Albuquerque ABQNV001 NM E0472167002Prospect Place Northeast Albuquerque ABQNV001 NM O0028327002 Prospect Place Northeast Albuquerque ABQNV001 NM E0533403400 Murray Street Alexandria AEXNV001 LA E0336983400 Murray Street Alexandria AEXNV001 LA E0342623400 Murray Street Alexandria AEXNV001 LA E035467and TABLE2:Address City Server State SID CODE7002 Prospect Place Northeast Albuquerque ABQNV001 NM F020181 PD7002 Prospect Place Northeast Albuquerque ABQNV001 NM F020181 PD7002 Prospect Place Northeast Albuquerque ABQNV001 NM F020181 PD7002 Prospect Place Northeast Albuquerque ABQNV001 NM F001949 ZX7002 Prospect Place Northeast Albuquerque ABQNV001 NM F001949 ZX7002 Prospect Place Northeast Albuquerque ABQNV001 NM F001949 ZX7002 Prospect Place Northeast Albuquerque ABQNV001 NM N074529 UV7002 Prospect Place Northeast Albuquerque ABQNV001 NM D146757 MO7002 Prospect Place Northeast Albuquerque ABQNV001 NM D146757 MO7002 Prospect Place Northeast Albuquerque ABQNV001 NM D146757 MO3400 Murray Street Alexandria AEXNV001 LA E033698 ZE3400 Murray Street Alexandria AEXNV001 LA E033698 ZE3400 Murray Street Alexandria AEXNV001 LA E033698 ZE3400 Murray Street Alexandria AEXNV001 LA E034262 FT3400 Murray Street Alexandria AEXNV001 LA E035467 GAI would like to display all 11 records (ONLY!!!) from TABLE1 and match them to TABLE2. Whener there is a match display CODE column. SO the output woul look like this:Address City Server State SID CODE7002 Prospect Place Northeast Albuquerque ABQNV001 NM F020181 PD7002 Prospect Place Northeast Albuquerque ABQNV001 NM F001949 ZX7002 Prospect Place Northeast Albuquerque ABQNV001 NM N074529 UV7002 Prospect Place Northeast Albuquerque ABQNV001 NM D146757 MO7002 Prospect Place Northeast Albuquerque ABQNV001 NM E0461977002 Prospect Place Northeast Albuquerque ABQNV001 NM E0472167002Prospect Place Northeast Albuquerque ABQNV001 NM O0028327002 Prospect Place Northeast Albuquerque ABQNV001 NM E0533403400 Murray Street Alexandria AEXNV001 LA E033698 ZE3400 Murray Street Alexandria AEXNV001 LA E034262 FT3400 Murray Street Alexandria AEXNV001 LA E035467 GAas you can see I did not change the # of rows from table 1. If there is no match, the query would return no match. How would I accomplish this. I was thinking about using SID column. Thank you very much. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-25 : 18:55:04
|
| Select tb1.Address,tb1.City,tb1.Server,tb1.State,tb1.SID,coalesce(tb2.Code,'')as Codefrom table1 tb1 left outer join table2 tb2on tb1.Address =tb2.Address and tb1.City = tb2.City and tb1.Server=tb2.Server and tb1.State =tb2.State and tb1.SID = tb2.SID |
 |
|
|
jszulc
Yak Posting Veteran
66 Posts |
Posted - 2008-09-25 : 22:08:29
|
| Thank you. Problem solved! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-26 : 01:28:14
|
| You should be taking only distinct records from TABLE2 for joining else you will get lot of duplicates in result. |
|
|
|
|
|
|
Microsoft 2005 SQL Query solution needed