Select records from two child tables

hello experts,

i have 3 tables names parent, child1, child2 

parent has 1 record, child1 has 2 record and child 3 has 3 records

the script and sample data is attached here 

select Parent.*,child1.f1,child2.f2  from child1 inner join Parent on parent.id =child1.id 
inner join child2 on child1.id  =child2.id 

running above query gives me sixes rows but i want only all rows of childs but not their Cartesian products

can u please  help to solve this puzzle foe me

/****** Object:  Table [dbo].[Parent]    Script Date: 06/18/2015 17:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Parent](
	[id] [int] NOT NULL,
	[vch_date] [datetime] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Parent] ([id], [vch_date]) VALUES (1, CAST(0x0000A30000000000 AS DateTime))
/****** Object:  Table [dbo].[child2]    Script Date: 06/18/2015 17:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[child2](
	[id] [int] NOT NULL,
	[f2] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[child2] ([id], [f2]) VALUES (1, 100)
INSERT [dbo].[child2] ([id], [f2]) VALUES (1, 200)
INSERT [dbo].[child2] ([id], [f2]) VALUES (1, 300)
/****** Object:  Table [dbo].[child1]    Script Date: 06/18/2015 17:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[child1](
	[id] [int] NOT NULL,
	[f1] [int] NOT NULL,
 CONSTRAINT [PK_chil1] PRIMARY KEY CLUSTERED 
(
	[id] ASC,
	[f1] 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
INSERT [dbo].[child1] ([id], [f1]) VALUES (1, 100)
INSERT [dbo].[child1] ([id], [f1]) VALUES (1, 200)


select Parent.*,child1.f1,child2.f2  from child1 inner join Parent on parent.id =child1.id 
inner join child2 on child1.id  =child2.id 

June 18th, 2015 8:38am

What is expected result? 

inner join child2 on parent.id  =child2.id 

Free Windows Admin Tool Kit Click here and download it now
June 18th, 2015 8:44am

dear Uri Dimant,




expected result is

id date f1 f2

1 2014-4-1 100 100

1 2014-4-1 200 200

1 2014-4-1 null 300

June 18th, 2015 10:44am

Hi,

Try with the below code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE ##Parent(
[id] [int] NOT NULL,
[vch_date] [datetime] NOT NULL
) ON [PRIMARY]
GO
INSERT ##Parent ([id], [vch_date]) VALUES (1, CAST(0x0000A30000000000 AS DateTime))
/****** Object:  Table [dbo].[child2]    Script Date: 06/18/2015 17:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE ##child2(
[id] [int] NOT NULL,
[f2] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT ##child2 ([id], [f2]) VALUES (1, 100)
INSERT ##child2 ([id], [f2]) VALUES (1, 200)
INSERT ##child2 ([id], [f2]) VALUES (1, 300)
/****** Object:  Table [dbo].[child1]    Script Date: 06/18/2015 17:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE ##child1(
[id] [int] NOT NULL,
[f1] [int] NOT NULL,
 CONSTRAINT [PK_chil1] PRIMARY KEY CLUSTERED 
(
[id] ASC,
[f1] 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
INSERT ##child1 ([id], [f1]) VALUES (1, 100)
INSERT ##child1 ([id], [f1]) VALUES (1, 200)


select * from ##child1
select * from ##child2
select * from ##Parent

select a.id,c.vch_date,b.f1,a.f2 from 
##child2 a
left join ##child1 b on a.id=b.id and a.f2=b.f1
left join ##Parent c on c.id=a.id

Free Windows Admin Tool Kit Click here and download it now
June 18th, 2015 12:57pm

Hi Sushil Agarwal,

I got what you need. Please execute the following code below!

declare @Parent TABLE (
	[id] [int] NOT NULL,
	[vch_date] [datetime] NOT NULL
) 

declare @child2 TABLE (
	[id] [int] NOT NULL,
	[f2] [int] NOT NULL
) 


declare @child1 TABLE (
	[id] [int] NOT NULL,
	[f1] [int] NOT NULL)


INSERT @Parent ([id], [vch_date]) VALUES (1, CAST(0x0000A30000000000 AS DateTime))

INSERT @child2 ([id], [f2]) VALUES (1, 100)
INSERT @child2 ([id], [f2]) VALUES (1, 200)
INSERT @child2([id], [f2]) VALUES (1, 300)

INSERT @child1 ([id], [f1]) VALUES (1, 100)
INSERT @child1([id], [f1]) VALUES (1, 200)



Select 
	parent.id,
	convert(date,parent.vch_date),
	q1.value as f1,
	q2.value as f2 
from 
(
	select row_number() over (order by id) [key], id,f1 as value from @child1
) as q1
right join 
(
	select row_number() over (order by id)  [key], id,f2 as value from @child2
) as q2
on 
	q2.[key]=q1.[key]
inner join 
	@Parent parent
on
	parent.id=q2.id

Result

Best Regards

June 18th, 2015 1:37pm

Try this:

DECLARE @parent TABLE (id int NOT NULL, vch_date datetime NOT NULL) 
INSERT INTO @parent (id, vch_date) VALUES (1, CAST(0x0000A30000000000 AS DateTime))

DECLARE @child2 TABLE (id int NOT NULL, f2 int NOT NULL)
INSERT INTO @child2 (id, f2) VALUES 
(1, 100),(1, 200),(1, 300)

DECLARE @child1 TABLE(id int NOT NULL, f1 int NOT NULL)
INSERT INTO @child1 (id, f1) VALUES 
(1, 100),(1, 200)

SELECT DISTINCT p.id, p.vch_date AS date, c1.f1, c2.f2
  FROM @parent p
	LEFT OUTER JOIN @child2 c2
	  ON p.id = c2.id
    LEFT OUTER JOIN @child1 c1
	  ON p.id = c1.id
	  AND c1.f1 = c2.f2
ORDER BY f2

Free Windows Admin Tool Kit Click here and download it now
June 18th, 2015 1:53pm

Thank you so much Ricardo

i have shown you only one record in parent table for trial if there is number of records in parents and references table  then not working your query Please see the below example

declare @Parent TABLE (
	[id] [int] NOT NULL,
	[vch_date] [datetime] NOT NULL
) 

declare @child2 TABLE (
	[id] [int] NOT NULL,
	[f2] [int] NOT NULL
) 


declare @child1 TABLE (
	[id] [int] NOT NULL,
	[f1] [int] NOT NULL)


INSERT @Parent ([id], [vch_date]) VALUES (1, CAST(0x0000A30000000000 AS DateTime))

INSERT @Parent ([id], [vch_date]) VALUES (2, CAST(0x0000A30000000000 AS DateTime))

INSERT @child2 ([id], [f2]) VALUES (1, 100)
INSERT @child2 ([id], [f2]) VALUES (1, 200)
INSERT @child2([id], [f2]) VALUES (1, 300)

INSERT @child1 ([id], [f1]) VALUES (1, 100)
INSERT @child1([id], [f1]) VALUES (1, 200)


INSERT @child2 ([id], [f2]) VALUES (2, 4000)
INSERT @child2 ([id], [f2]) VALUES (2, 5000)
 

INSERT @child1 ([id], [f1]) VALUES (2, 1000)
INSERT @child1([id], [f1]) VALUES (2, 2000)
INSERT @child1([id], [f1]) VALUES (2, 3000)



Select 
	parent.id,
	convert(date,parent.vch_date),
	q1.value as f1,
	q2.value as f2 
from 
(
	select row_number() over (order by id) [key], id,f1 as value from @child1
) as q1
right join 
(
	select row_number() over (order by id)  [key], id,f2 as value from @child2
) as q2
on 
	q2.[key]=q1.[key]
inner join 
	@Parent parent
on
	parent.id=q2.id

 
June 19th, 2015 1:22am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics