April 30, 2003 at 6:29 am
I have constructed two select-statements, which in my opinion should do the same (see below). The first query results in two records, the second has only one.
Can anyone explain the difference?
Thanks,
Erik
The queries:
declare @ContractID int
set @ContractID = 3979
select csh.*, Logdata.*
from TblContractServiceHistorie csh
left join TblLog Logdata
on ( Logdata.Code1 = csh.ID
and Logdata.TabelNaam = 'TblContractServiceHistorie'
and Logdata.LogType = 5 )
where csh.ContractID = @ContractID
order by csh.ContractID, csh.ID
select csh.*, Logdata.*
from TblContractServiceHistorie csh
left join TblLog Logdata
on Logdata.Code1 = csh.ID
where (csh.ContractID = @ContractID or @ContractID is null)
and (Logdata.LogType = 5 or Logdata.LogType is null)
and (Logdata.TabelNaam = 'TblContractServiceHistorie' or Logdata.TabelNaam is null)
order by csh.ContractID, csh.ID
Script to create the tables:
CREATE TABLE [dbo].[TblLog] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[LogType] [int] NOT NULL ,
[TabelNaam] [varchar] (100) NOT NULL ,
[Code1] [int] NOT NULL ,
[Code2] [int] NULL ,
[Omschrijving] [text] NULL ,
[Gebruiker] [varchar] (50) NOT NULL ,
[Computer] [varchar] (50) NOT NULL ,
[LogTijd] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblLog] WITH NOCHECK ADD
CONSTRAINT [PK_TblLog] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
SET IDENTITY_INSERT TblLog ON
GO
INSERT INTO TblLog (ID, LogType, TabelNaam, Code1, Code2, Omschrijving, Gebruiker, Computer, LogTijd)
VALUES(1, 2, 'TblDebiteur', 1, NULL, 'Change', 'User1', 'WSID1', '2002-05-28 13:25:12.390')
INSERT INTO TblLog (ID, LogType, TabelNaam, Code1, Code2, Omschrijving, Gebruiker, Computer, LogTijd)
VALUES(2, 2, 'TblContract', 1, NULL, 'Change', 'User1', 'WSID1', '2003-02-17 12:47:50.043')
INSERT INTO TblLog (ID, LogType, TabelNaam, Code1, Code2, Omschrijving, Gebruiker, Computer, LogTijd)
VALUES(3, 5, 'TblContractServiceHistorie', 2, 3979, 'Change in ServiceID (105 --> 18) for Contract 3979', 'User1', 'WSID1', '2002-12-31 13:49:32.140')
INSERT INTO TblLog (ID, LogType, TabelNaam, Code1, Code2, Omschrijving, Gebruiker, Computer, LogTijd)
VALUES(4, 3, 'TblContract', 2, NULL, 'New', 'User1', 'WSID1', '2002-11-07 10:54:32.153')
INSERT INTO TblLog (ID, LogType, TabelNaam, Code1, Code2, Omschrijving, Gebruiker, Computer, LogTijd)
VALUES(5, 2, 'TblContract', 2, NULL, 'Change', 'User1', 'WSID1', '2002-11-07 10:55:28.013')
GO
SET IDENTITY_INSERT TblLog OFF
GO
CREATE TABLE [dbo].[TblContractServiceHistorie] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ContractID] [int] NOT NULL ,
[ServiceID] [int] NULL ,
[DatumStart] [datetime] NULL ,
[DatumEind] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblContractServiceHistorie] WITH NOCHECK ADD
CONSTRAINT [PK_TblContractServiceHistorie] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_TblContractServiceHistorie_GeldigInterval] CHECK ([DatumStart] <= [DatumEind])
GO
SET IDENTITY_INSERT TblContractServiceHistorie ON
GO
INSERT INTO TblContractServiceHistorie (ID, ContractID, ServiceID, DatumStart, DatumEind)
VALUES(1, 3979, 105, NULL, '2002-12-30 00:00:00.000')
INSERT INTO TblContractServiceHistorie (ID, ContractID, ServiceID, DatumStart, DatumEind)
VALUES(2, 3979, 18, '2002-12-31 00:00:00.000', NULL)
SET IDENTITY_INSERT TblContractServiceHistorie OFF
GO
April 30, 2003 at 7:48 am
I think the answer to this is down to where the filtering of data is done.
The query using the where clause (your second example) is filtering the TblLog rows before they are being joined. There are actually no rows in TblLog with a null LogType or null TabelNaam.
The first example isn't actually filtering TblLog. When the join is attempted, there is no matching row in TblLog, so the values for TblLog are being replaced with null at that point.
April 30, 2003 at 8:16 am
Exactly. With an outer join where you're looking for the missing records, you need to put all the other criteria in the join, just check for null in the where.
Andy
April 30, 2003 at 11:50 pm
This explains, I was not aware of this filtering before the join.
Thanks for you reactions.
Erik
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply