Left Join with reference data.

Question

Left Join with reference data.

SSC-Addicted

Points: 407

July 15, 2022 at 2:16 pm

Records  Refrenace        |  DataMaster            DataKey
1            A            |    A                       22
2            B            |    B                        33
3            C            |    C                        44
4            E            | 
5            F            |
6            G            |
7            H            |
8            D            |
9            R            |


For the Above which is not in the Master data I need to set it as 'Unknown'

Record       data                       Key
1            A                           22
2            B                          33
3            C                          44
4          Unknown                      -1
5          Unknown                      -1
6          Unknown                      -1
7          Unknown                      -1
8          Unknown                      -1
9          Unknown                      -1

Hi,

How to get the with the help of Left Join the below data,

i need to get which ever is not in the master data should be as unknown and -1.

This topic was modified 2 years, 4 months ago by LearnSQL.
This topic was modified 2 years, 4 months ago by LearnSQL.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 1

My question would be, why are you using two tables to do one thing? It looks to me like it should be a single table because there appears to be a 1:1 correlation according to the data you've posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LearnSQL SSC-Addicted Points: 407 More actions · Answer 2

Ref table is something we get it predefined , where as master data is something we will be getting with some extra data sometime.

But now i think i had done.

declare @Master table ( data varchar(15), datakey int)
declare @ref table ( rid int, data varchar(15))
Insert into @Master
Select 'A', 22
UNION ALL 
Select 'B', 33
UNION ALL 
Select 'C', 44

insert into @ref 
Select 1, 'A'
UNION ALL 
Select 2, 'B'
UNION ALL 
Select 3, 'C'
UNION ALL 
Select 4, 'D'
UNION ALL 
Select 5, 'E'
UNION ALL 
Select 6, 'F'
UNION ALL 
Select 7, 'G'
UNION ALL 
Select 8, 'R'

select isnull(b.data,'Unknown'),isnull(b.datakey,'-1') from @ref a 
left join @Master b on a.data=b.data

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

Jeff Moden wrote:

My question would be, why are you using two tables to do one thing? It looks to me like it should be a single table because there appears to be a 1:1 correlation according to the data you've posted.

It's vertical partitioning. There are often good reasons to do it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

MMartin1 One Orange Chip Points: 27795 More actions · Answer 4

Thank you Scott. I now know the naming of this type of scheme. I've seen it where the pk object has different properties based on the value in another column , 'type' related. So a master table is created with related tables.

An example would be having a table of music you have. All songs will have artist, year, name... your master table. But those of cd media type will have other columnd like track number, while those on your computer (mp3) will have mb size, folder location .. different remaining properties. So these will be different tables.

I think another advantage is faster write speeds are possible.

----------------------------------------------------