January 23, 2020 at 10:32 pm
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
what i have tried
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]
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)
insert into [MasterData]
([SourceGeneralTypeID],[StatusGeneralTypeID],[DailyLogId])
values
(2,30,20),
(2,30,30),
(2,30,10)
select M.MasterDataID,M.TrackingNumber,M.StatusDate
from #TempMaster tmp
INNER join [MasterData] M on ???=?????
where ??????
January 24, 2020 at 10:12 am
can any one help me on that
January 24, 2020 at 11:29 am
I think you'll need a few ORs in your join predicate. Based on your sample data, what results do you expect from your SELECT query?
Where does the data in the temp table come from? We may be able to suggest a better way of doing this.
John
January 24, 2020 at 1:19 pm
If it's always going to be one of the three values, but not the other two, you could do a bunch of OR statements for the three different clauses. However, a UNION ALL query that duplicates your select statement, but does each distinct clause independently will probably work better. That way, each of the distinct statements gets an execution plan (a subset of the plan really, but effectively the same result).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply