April 17, 2013 at 10:57 am
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!
April 17, 2013 at 11:02 am
I think I got it using a full outer join.
April 22, 2013 at 9:25 am
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
April 22, 2013 at 3:59 pm
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.
April 22, 2013 at 4:06 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply