T-SQL Join - Select Next Date Issue

Giganews Newsgroups
Subject: T-SQL Join - Select Next Date Issue
Posted by:  Klaus Fischer (klausfischer0…@gmail.com)
Date: Wed, 24 Aug 2011

Hello

I'm trying to solve the following problem:

I have a Job table and a JobActions table and would like to join both
and select the next action date from the JobActions table.

Below the SQL script to create the tables and insert some data rows.
At the bottom the SELECT statment which should return only always only
one row for each JobID even if there is no next available action date
(should return NULL values in this case for JobActionID and
JobActionDate)

Your help would be very much appreciated

Thanks

Klaus

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
    [JobID] [int] NOT NULL,
    [JobName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED
(
    [JobID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[JobActions]    Script Date: 08/24/2011
09:57:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JobActions](
    [JobActionID] [int] NOT NULL,
    [JobID] [int] NOT NULL,
    [JobActionDate] [datetime] NOT NULL,
CONSTRAINT [PK_JobActions_1] PRIMARY KEY CLUSTERED
(
    [JobActionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  ForeignKey [FK_JobActions_Jobs]    Script Date:
08/24/2011 09:57:09 ******/
ALTER TABLE [dbo].[JobActions]  WITH CHECK ADD  CONSTRAINT
[FK_JobActions_Jobs] FOREIGN KEY([JobID])
REFERENCES [dbo].[Jobs] ([JobID])
GO
ALTER TABLE [dbo].[JobActions] CHECK CONSTRAINT [FK_JobActions_Jobs]
GO

INSERT INTO Jobs VALUES (1, 'Job A')
INSERT INTO Jobs VALUES (2, 'Job B')
INSERT INTO Jobs VALUES (3, 'Job C')
GO

INSERT INTO JobActions VALUES (1,1,'2011/01/01')
INSERT INTO JobActions VALUES (2,1,'2012/01/01')
INSERT INTO JobActions VALUES (3,1,'2011/08/23')
INSERT INTO JobActions VALUES (4,1,'2011/09/01')
INSERT INTO JobActions VALUES (5,2,'2011/01/01')
INSERT INTO JobActions VALUES (6,2,'2012/01/01')
GO

SELECT Jobs.JobID, Jobs.JobName, JobActions.JobActionID,
JobActions.JobActionDate FROM Jobs LEFT JOIN JobActions ON Jobs.JobID
= JobActions.JobID
GO

Replies