Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help please Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 10:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:57 AM
Points: 219, Visits: 721
This is probably a simple join issue but here goes...

We have a system (HAL) that does not contain the Dr badge number. The Dr badge number is housed in another system. A co-worker sent me all Doctor F_Name, L_Name, M_Inital, Dr_Badge_Num in a speadsheet. I imported these 4 columns into a table (DR) in the (HAL) database so I could join HAL.F_Name = DR.F_Name and HAL.L_Name = DR.L_Name and HAL.M_Initial = DR.M_Inital so I could pull in the Dr_Badge_Num Column.

However this restricts my results to only existing DR table entires. Since the DR table is static and not updated I need my results to show none matching HAL.x as Null in the Dr_Badge_Num column.

Example:

Static DR Table
f_name, m_initial, l_name, dr_num
1. John A. White 23456
2. Tom B. Smith 65432
3. Jack T. Murfy 12345

HAL Table w/o Dr Number field
f_name, m_initial, l_name
1. John A. White
2. Tom B. Smith
3. Jack T. Murfy
4. Dennis K. Huges


Results:
f_name, m_initial, l_name, dr_num
1. John A. White 23456
2. Tom B. Smith 65432
3. Jack T. Murfy 12345
4. Dennis K. Huges NULL


Thank you!




Post #1443386
Posted Wednesday, April 17, 2013 11:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:57 AM
Points: 219, Visits: 721
I think I got it using a full outer join.
Post #1443389
Posted Monday, April 22, 2013 9:25 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 431, Visits: 1,744
I don't have a way to test this, but I had to do something similar with DOB matching on names between two tables recently, and used something like:

select h.f_name, h.m_initial, h.l_name,
case
when h.f_name = d.f_name
and h.m_initial = d.m_initial
and h.l_name = d.l_name then d.dr_num
else null end
as 'dr_num'
from hal h,
dr d

Post #1445035
Posted Monday, April 22, 2013 3:59 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,467, Visits: 6,438
Vertigo44 (4/17/2013)
I think I got it using a full outer join.


Yes, it would, but it is not necessarily quite the thing you want, unless you want to see all the missing DRs that are HALs as well. What you really are after (from what I guess from your description) is either a LEFT OUTER JOIN or a RIGHT OUTER JOIN.

LEFT OUTER JOIN (or LEFT JOIN for short) gives you a row from every row in the left-hand table (the one mentioned in the FROM clause), no matter whether there is a row matching in the right-hand table (the one in the LEFT JOIN clause), where if there is no match all columns from the right-hand side will be NULL.

RIGHT OUTER JOIN (or RIGHT JOIN for short) works the opposite way. You get a row for each row in the JOIN, no matter whether there is a matching row in the FROM, but the left-hand ones will be NULLs.

FULL OUTER JOIN combines the two traits, in that you will always get both the left and right hand sides, with NULLs on the opposite end if there is no match.

So, if you want to see the HALs with no DRs (which is your master data table) and you expect new Drs to come on board, I'd select from DRs and right-join to HAL and Dr. Dennis K. Huges will appear as well.

Did this help?

Edit: Finished off the last sentence.


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1445222
Posted Monday, April 22, 2013 4:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,467, Visits: 6,438
BTW, personally I would find it much more intuitive to work from the left-hand side, where that one would be the one where I'd expect to have more new rows. In your case that would be the HAL table, since there could be new doctors, so HAL would be in my FROM clause and DR in the LEFT OUTER JOIN.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1445224
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse