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