Return only One Row From Dataset I have three tables and all tables hold a primary key of empnum this is an image of my desired result set


However my query is returning 9 rows instead of 3!  See screenshot


This is my DDL -->

CREATE TABLE [dbo].[__One](
    [empnum] [char](12) NOT NULL,
    [empid] [char](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[__Three](
    [empnum] [char](12) NOT NULL,
    [empjobdesc] [char](64) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[__Two](
    [empnum] [char](12) NOT NULL,
    [empprimarydutydesc] [char](64) NULL,
    [emphourssched] [int] NULL,
    [empdesiredsellamt] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[__One] ([empnum], [empid]) VALUES (N'ec28191', NULL)
GO
INSERT [dbo].[__Three] ([empnum], [empjobdesc]) VALUES (N'ec28191', N'Rigalo')
GO
INSERT [dbo].[__Three] ([empnum], [empjobdesc]) VALUES (N'ec28191', N'Fitzgerald')
GO
INSERT [dbo].[__Three] ([empnum], [empjobdesc]) VALUES (N'ec28191', N'Gooey Glue')
GO
INSERT [dbo].[__Two] ([empnum], [empprimarydutydesc], [emphourssched], [empdesiredsellamt]) VALUES (N'ec28191', N'RGM', 23, 5520)
GO
INSERT [dbo].[__Two] ([empnum], [empprimarydutydesc], [emphourssched], [empdesiredsellamt]) VALUES (N'ec28191', N'FZG', 12, 4840)
GO
INSERT [dbo].[__Two] ([empnum], [empprimarydutydesc], [emphourssched], [empdesiredsellamt]) VALUES (N'ec28191', N'GGRW ', 19, 5000)
GO


And this is the query I write that returns the 9 rows instead of the 3...

;With CTE As (
Select * from __One
),
CTE2 As (
Select * from __Three
), CTE3 As (
Select * from __Two
)
Select Distinct a.empnum, a.empid, c.empprimarydutydesc, b.empjobdesc, c.emphourssched
FROM CTE a
LEFT OUTER JOIN CTE2 b
ON a.empnum = b.empnum
LEFT OUTER JOIN CTE3 c
ON b.empnum = c.empnum


What in the syntax needs to be changed in order to only return my desired 3 rows?
All of your employee numbers are the same. So right now every row will join to every row (1 * 3 * 3 = 9)  and nothing ties tables 2 and 3 together (for example a foreign key to show that FZG belongs to Fitzgerald).  Adding the foreign key will fix the problem you are seeing.