January 10, 2014 at 3:29 am
I have data where i need to Show only Parent and 1st Child
====================
CREATE TABLES
====================
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Travel_Quantity](
[Load_Id] [int] NULL,
[Mode_Sno] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Parent_Sno] [int] NULL,
[QA] [numeric](18, 0) NULL
) ON [PRIMARY]
====================
INSERT DATA TABLE 1
====================
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AP' ,'AIR' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SE' ,'SEA' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SP' ,'SHIP' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BT' ,'BOAT' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'RD' ,'ROAD' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BU' ,'BUS' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'CA' ,'CAR' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BI' ,'BIKE' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'CY' ,'CYCLE' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'TR' ,'TRAM' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BM' ,'BMW' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AI' ,'AUDI' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BMC' ,'BMW-C' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BMS' ,'BMW-S' ,'No')
====================
INSERT DATA TABLE 2
====================
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'1' ,'AP' ,'-1','4' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'2' ,'SE' ,'-1','2' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'3' ,'SP' ,'2','3' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'4' ,'BT' ,'2','4' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'5' ,'RD' ,'-1','2' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'6' ,'BU' ,'5','7' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'7' ,'CA' ,'5','3' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'8' ,'BI' ,'5','15' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'9' ,'CY' ,'5','2' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'10' ,'TR' ,'5','5' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'11' ,'BM' ,'7','6' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'12' ,'AI' ,'7','14' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'13' ,'BM' ,'11','6' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'14' ,'AI' ,'12','14' )
====================
EXPECTED RESULT
====================
If i join the records, i will get Data displayed for 14 rows.
Since my Report needs only Parent and 1st Child, I am Expecting only 10 Rows based on Mode_Detail.
Expected Result
Mode_Info |Mode_Detail |QA
Air | |4
Sea | |2
SEA |SHIP |3
SEA |BOAT |4
ROAD| | 2
ROAD |BUS |7
ROAD |CAR |3
ROAD |BIKE |15
ROAD |CYCLE |2
ROAD |TRAM |5
January 13, 2014 at 4:19 pm
You can do it using CROSS APPLY.
Read these two articles (definitely worth the read!):
http://qa.sqlservercentral.com/articles/APPLY/69953/ (Part I)
http://qa.sqlservercentral.com/articles/APPLY/69954/ (Part II)
(Basic plan)
Get the TOP 1 Values from the child table (pass in the parentID)
Then join back to the parent table.
Here's an example, (so I am sure it works!):
My basic structure is this:
Protocol---(1,M)--Enroll
Here's my example code...
USE tempdb;
GO
/****** Object: Table [dbo].[Enroll] Script Date: 1/13/2014 5:40:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Enroll](
[enrollmentID] [int] IDENTITY(10000,1) NOT NULL,
[e_PatientID] [int] NOT NULL,
[e_ProtocolNo] [varchar](30) NOT NULL,
[enrollDate] [datetime] NULL,
[LeaveDate] [datetime] NULL,
CONSTRAINT [PK_Enroll] PRIMARY KEY CLUSTERED
(
[enrollmentID] 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
SET ANSI_PADDING OFF
GO
-- Enroll is the CHILD table.
ALTER TABLE [dbo].[Enroll] WITH CHECK ADD CONSTRAINT [FK_Enroll_Protocol] FOREIGN KEY([e_ProtocolNo])
REFERENCES [dbo].[Protocol] ([ProtocolNo])
GO
USE tempdb;
GO
/****** Object: Table [dbo].[Protocol] Script Date: 1/13/2014 5:42:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Protocol](
[ProtocolNo] [varchar](30) NOT NULL,
[OpenToEnrollDate] [datetime] NULL,
[CloseToEnrollDate] [datetime] NULL,
[EnrollmentGoal] [int] NULL,
[CTCVersion] [int] NULL,
CONSTRAINT [PK_Protocol] PRIMARY KEY CLUSTERED
(
[ProtocolNo] 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
SELECT p.ProtocolNo
, p.OpenToEnrollDate
, p.CloseToEnrollDate
, x.enrollmentID
, x.enrollDate
FROM Protocol p
CROSS APPLY (
SELECT TOP 1 enrollmentID
, enrollDate
FROM enroll
WHERE e_ProtocolNo=p.ProtocolNo) x;
Hope this helps.
Pieter
January 13, 2014 at 9:01 pm
Thanks for the link and scripts.
January 13, 2014 at 9:12 pm
You're welcome... if you run my code, make sure the CREATE TABLE code for Protocol is before the code for Enroll. (That part was confusing in my post, I thought... )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply