LEFT OUTER JOIN on 2 fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

LEFT OUTER JOIN on 2 fields

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-02-03 : 13:30:10

Is is possible to have LEFT OUTER JOIN comparing 2 fields rather than one?

ON T1.PropertyIDStartLoc = T2.PropertyID

I would like to have something of that nature.
ON T1.PropertyIDStartLoc = T2.PropertyID
AND T1.PropertyIDFinishLoc = T2.PropertyID

I need to display Start Locations and Finish locations with City, Address and state. I can only display Start locations so far.

ALTER PROCEDURE [dbo].[sp_DisplayKPItbl]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT T1.TaskID,T1.Lead, T1.WRM, T1.PAR, T1.PTDB,T1.PThree,T1.PM,T1.RequestingLOB, T1.LOB,T1.PropertyIDStartLoc, T2.Address as [Start Location Address], T2.City as [Start Location City],
T2.State as [Start Location State], T1.Description, T1.ProjectType,T1.ServerName,T1.ServerType,T1.Responsible,
T1.Status,T1.RAG,T1.StartDates,T1.EndDates,T1.TreeorDomainImpacted,T1.NumOfSites, T1.NumOfUsers,T1.GBdatamoved,
T1.GBdatadeleted,T1.NumOfSrvrsAdded,T1.NumOfSrvrsDecommed,T1.NumOfAppsDeployed,T1.EUTEngineeringConsult,
T1.Comments, T1.TimeSpend,T1.Complexity,T1.LastUpdated,T1.StatusCompletedDate,T1.StatusCancelledDate,
T1.CreatedDate
FROM
dbo.KPITbl T1 LEFT OUTER JOIN dbo.RealEstate T2 ON T1.PropertyIDStartLoc = T2.PropertyID
END

Here are tables:
+++++++++++++++++++++++++++++
CREATE TABLE [dbo].[KPITbl](
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[Lead] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WRM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PTDB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAR] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RequestingLOB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOB] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartLocationState] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinishLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Responsible] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RAG] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDates] [smalldatetime] NULL,
[EndDates] [smalldatetime] NULL,
[TreeorDomainImpacted] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfSites] [int] NULL,
[NumOfUsers] [int] NULL,
[GBdatamoved] [int] NULL,
[GBdatadeleted] [int] NULL,
[NumOfSrvrsAdded] [int] NULL,
[NumOfSrvrsDecommed] [int] NULL,
[NumOfAppsDeployed] [int] NULL,
[EUTEngineeringConsult] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TimeSpend] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Complexity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastUpdated] [smalldatetime] NULL,
[DCOorSTANDALONE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ECM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatusCompletedDate] [smalldatetime] NULL,
[StatusCancelledDate] [smalldatetime] NULL,
[CreatedDate] [smalldatetime] NULL,
[StartLocationAddress] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartLocationCity] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PropertyIDStartLoc] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PropertyIDFinishLoc] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PThree] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Sheet1a] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

++++++++++++++++++++++++++++++++++++++++++++++++

USE [KPI]
GO
/****** Object: Table [dbo].[RealEstate] Script Date: 02/03/2008 12:28:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RealEstate](
[PID] [int] IDENTITY(1,1) NOT NULL,
[PropertyID] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_RealEstate] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
++++++++++++++++++++++++++++++++++

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-03 : 14:38:38

Why don't you?
You can have several join conitions.

E 12°55'05.25"
N 56°04'39.16"

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-02-03 : 14:47:06

If I do this:
ALTER PROCEDURE [dbo].[sp_DisplayKPItbl2]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT T1.TaskID,T1.Lead, T1.WRM, T1.PAR, T1.PTDB,T1.PThree,T1.PM,T1.RequestingLOB, T1.LOB,T1.PropertyIDStartLoc,T1.PropertyIDFinishLoc, T2.Address as [Start Location Address], T2.City as [Start Location City],
T2.State as [Start Location State],T2.Address as [Finish Location Address], T2.City as [Finish Location City],
T2.State as [Finish Location State], T1.Description, T1.ProjectType,T1.ServerName,T1.ServerType,T1.Responsible,
T1.Status,T1.RAG,T1.StartDates,T1.EndDates,T1.TreeorDomainImpacted,T1.NumOfSites, T1.NumOfUsers,T1.GBdatamoved,
T1.GBdatadeleted,T1.NumOfSrvrsAdded,T1.NumOfSrvrsDecommed,T1.NumOfAppsDeployed,T1.EUTEngineeringConsult,
T1.Comments, T1.TimeSpend,T1.Complexity,T1.LastUpdated,T1.StatusCompletedDate,T1.StatusCancelledDate,
T1.CreatedDate
FROM
dbo.KPITbl T1 FULL OUTER JOIN dbo.RealEstate T2 ON T1.PropertyIDStartLoc = T2.PropertyID
and
T1.PropertyIDFinishLoc = T2.PropertyID
END

I get 7855 returns, "the left table" has only 833 that is why I need always 833 records even if these Start finish locations are null. :-). Also T2.Address as [Start Location Address] column returns same address as T2.Address as [Finish Location Address].

Start location and Finish Location are usually different. But the query takes only [Start Location *]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-03 : 14:55:42

Why did you change LEFT JOIN to FULL JOIN?

E 12°55'05.25"
N 56°04'39.16"

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-02-03 : 15:01:37

Oops I was testing something. With LEFT JOIN I get right number of records BUT the locations are NULL for all Start and Finish columns.
ALTER PROCEDURE [dbo].[sp_DisplayKPItbl2]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT T1.TaskID,T1.Lead, T1.WRM, T1.PAR, T1.PTDB,T1.PThree,T1.PM,T1.RequestingLOB, T1.LOB,T1.PropertyIDStartLoc,T1.PropertyIDFinishLoc, T2.Address as [Start Location Address], T2.City as [Start Location City],
T2.State as [Start Location State],T2.Address as [Finish Location Address], T2.City as [Finish Location City],
T2.State as [Finish Location State], T1.Description, T1.ProjectType,T1.ServerName,T1.ServerType,T1.Responsible,
T1.Status,T1.RAG,T1.StartDates,T1.EndDates,T1.TreeorDomainImpacted,T1.NumOfSites, T1.NumOfUsers,T1.GBdatamoved,
T1.GBdatadeleted,T1.NumOfSrvrsAdded,T1.NumOfSrvrsDecommed,T1.NumOfAppsDeployed,T1.EUTEngineeringConsult,
T1.Comments, T1.TimeSpend,T1.Complexity,T1.LastUpdated,T1.StatusCompletedDate,T1.StatusCancelledDate,
T1.CreatedDate
FROM
dbo.KPITbl T1 LEFT OUTER JOIN dbo.RealEstate T2 ON T1.PropertyIDStartLoc = T2.PropertyID
and
T1.PropertyIDFinishLoc = T2.PropertyID
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources