Click here to Skip to main content
14,691,039 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have #TempMaster temp table have 3 fields

with optional values on 3 fields SourceGeneralTypeID AND StatusGeneralTypeID AND DailyLogId
meaning
may be SourceGeneralTypeID have values and other 2 fields not have value
may be StatusGeneralTypeID have values and other 2 fields not have value
may be DailyLogId have values and other 2 fields not have value
so my problem
How to write on statement after join on ? = ? where ?

problem how to write On ? = ? where ???????????????
and what i write on where
based on details above

details data
create table #TempMaster(

SourceGeneralTypeID int,
StatusGeneralTypeID int,
DailyLogId  int
)
insert into #TempMaster

(SourceGeneralTypeID,StatusGeneralTypeID,DailyLogId)
values
(Null,10,20),
(2,Null,30),
(2,30,Null)


CREATE TABLE [MasterData](
	[MasterDataID] [int] IDENTITY(1,1) NOT NULL,
	[SourceGeneralTypeID] [int] NULL,
	[StatusGeneralTypeID] [int] NULL,
	[DailyLogId] [int] NULL,
 CONSTRAINT [PK_MasterData] PRIMARY KEY CLUSTERED 
(
	[MasterDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into [MasterData] 
([SourceGeneralTypeID],[StatusGeneralTypeID],[DailyLogId])
values
(2,30,20),
(2,30,30),
(2,30,10)


What I have tried:

select M.MasterDataID,M.TrackingNumber,M.StatusDate
 from #TempMaster tmp
INNER join [MasterData] M on ???=?????
where ??????
Posted
Updated 23-Jan-20 22:21pm
Comments
MadMyche 23-Jan-20 22:29pm
   
You are going to need to reword this question; as it just does not make sense.

I wouldn't try to write these in a single query. Join is not very dynamic in nature.

Instead, you can split this into three parts and use UNION (Transact-SQL) - SQL Server | Microsoft Docs[^] to combine the results. Consider the following
SELECT M.MasterDataID,M.TrackingNumber,M.StatusDate
FROM #TempMaster tmp
INNER JOIN [MasterData] M ON m.SourceGeneralTypeID = tmp.SourceGeneralTypeID
WHERE tmp.SourceGeneralTypeID IS NOT NULL
UNION ALL
SELECT M.MasterDataID,M.TrackingNumber,M.StatusDate
FROM #TempMaster tmp
INNER JOIN [MasterData] M ON m.StatusGeneralTypeID = tmp.StatusGeneralTypeID 
WHERE tmp.StatusGeneralTypeID IS NOT NULL
UNION ALL
SELECT M.MasterDataID,M.TrackingNumber,M.StatusDate
FROM #TempMaster tmp
INNER JOIN [MasterData] M ON m.DailyLogId = tmp.DailyLogId 
WHERE tmp.DailyLogId IS NOT NULL

In order for tihs to produce correct results, you need to adjust the conditions to match your requirements and the logic in the data in each three cases.

Note that to when you build this, you can run each select separately . This makes it easier to check that the intermediate results are correct for each SELECT statement. When running the whole statement only, it may be harder to see which part gives invalid results.
   
Wendelius solution is good if you want the condition m.SourceGeneralTypeID = tmp.SourceGeneralTypeID OR m.StatusGeneralTypeID = tmp.StatusGeneralTypeID OR m.DailyLogId = tmp.DailyLogId where any of the tmp-fields can be null.

But if you want an AND-condition you have to use a variant of this query:
SELECT  M.MasterDataID
       ,M.TrackingNumber
       ,M.StatusDate
FROM    MasterData M
JOIN    #TempMaster tmp
    ON  (m.SourceGeneralTypeID = tmp.SourceGeneralTypeID OR tmp.SourceGeneralTypeID IS NULL)
    AND (m.StatusGeneralTypeID = tmp.StatusGeneralTypeID OR tmp.StatusGeneralTypeID IS NULL)
    AND (m.DailyLogId          = tmp.DailyLogId          OR tmp.DailyLogId          IS NULL)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900