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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-04-08 : 08:32:34
|
| Jamie writes "In TableA I have (say) Name, Surname, EmailAddress. Any of the fields may be null.So my sample data could beName Surname EmailAddress==== ======= ============Joe.....Smith...........joe@smith.comBob.....Jones John....James...........john@james.comIn tableB I have some existing customer data relating to addresses, e.g. PersonID,Town,City,EmailAddressso some sample data might bePersonID........Town............County..EmailAddress========........====............======..============ 10001...........Monmouth........Gwent...joe@smith.com10002...........Abergavenny.....Gwent 11009...........Chepstow........Gwent What I want to return is all people in TableA, plus the PersonID of any row in TableB where the EmailAddressfield matches, or (say) "No match" if there isnt one, e.g.Name....Surname.........EmailAddress....PersonID ====....=======.........============....========Joe.....Smith...........joe@smith.com 10001Bob.....Jones...........................No MatchJohn....James...........john@james.com..No MatchThis is proving trickier than I thought - can any of you SQL guru's help me?Thanks,JA" |
|
|
Kaleem021
Starting Member
26 Posts |
Posted - 2005-04-08 : 09:08:13
|
| Try this Select t1.Name, t1.Surname, t1.EmailAddress, isnull(t2.personid,'No Match')from tableA left outer join tableB on t1.EmailAddress=t2.EmailAddress*****************************************************************************Myth BreakerKaleem021@hotmail.comDoing Nothing Is Very Hard To Do, You Never Know When You Are Finished. |
 |
|
|
Kaleem021
Starting Member
26 Posts |
Posted - 2005-04-08 : 09:09:39
|
| Alias tableA to t1 and tableB to t2*****************************************************************************Myth BreakerKaleem021@hotmail.comDoing Nothing Is Very Hard To Do, You Never Know When You Are Finished. |
|
|
|
|
|
|
|
|
Joins where both sides could be null...