SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help please


Help please

Author
Message
Vertigo44
Vertigo44
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 839
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!
Vertigo44
Vertigo44
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 839
I think I got it using a full outer join.
sqldriver
sqldriver
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 2517
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


Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2426 Visits: 6494
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)
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2426 Visits: 6494
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search