I am trying to figure out how to join 3 tables and be able to get the data I want. The first table is joined to the 2nd and 3rd tables. There is no direct relationship between the 2nd and 3rd tables:

Table 1:

CREATE TABLE [dbo].[Table1](
[Table1PK] [bigint] NOT NULL,
[Table2FK] [bigint] NULL,
[Table3FK] [bigint] NULL,
[SomeName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Table1PK] 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

ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_Table2] FOREIGN KEY([Table2FK])
REFERENCES [dbo].[Table2] ([Table2PK])
GO
ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_Table2]
GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_Table3] FOREIGN KEY([Table3FK])
REFERENCES [dbo].[Table3] ([Table3PK])
GO
ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_Table3]

Table 2:

CREATE TABLE [dbo].[Table2](
[Table2PK] [bigint] NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Table2PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Table 3:

CREATE TABLE [dbo].[Table3](
[Table3PK] [bigint] NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED
(
[Table3PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Here are the INSERTs:

INSERT INTO [PLAY].[dbo].[Table1]
([Table1PK]
,[Table2FK]
,[Table3FK]
,[SomeName])
VALUES
(<Table1PK, bigint,>
,<Table2FK, bigint,>
,<Table3FK, bigint,>
,<SomeName, varchar(50),>)

INSERT INTO [PLAY].[dbo].[Table2]
([Table2PK]
,[Name])
VALUES
(<Table2PK, bigint,>
,<Name, varchar(50),>)

INSERT INTO [PLAY].[dbo].[Table3]
([Table3PK]
,[Name])
VALUES
(<Table3PK, bigint,>
,<Name, varchar(50),>)

So the table contents look like:

Table1:

Table1PK Table2FK Table3FK SomeName
1 1 NULL Some1
2 2 NULL Some2
3 3 NULL Some3
4 NULL 1 Some4
5 NULL 2 Some5
6 NULL 3 Some6
7 1 1 Some7
8 2 2 Some8
9 3 3 Some9
10 NULL NULL Some10

Table2:

Table2PK Name
1 A
2 B
3 C

Table3:

Table3PK Name
1 D
2 E
3 F


If I do a LEFT join on both tables, I almost get what I want:

SELECT Table1.Table1PK, Table1.Table2FK, Table1.Table3FK, Table1.SomeName
FROM Table1 LEFT OUTER JOIN
Table2 ON Table1.Table2FK = Table2.Table2PK LEFT OUTER JOIN
Table3 ON Table1.Table3FK = Table3.Table3PK

Results in:

1 1 NULL Some1
2 2 NULL Some2
3 3 NULL Some3
4 NULL 1 Some4
5 NULL 2 Some5
6 NULL 3 Some6
7 1 1 Some7
8 2 2 Some8
9 3 3 Some9
10 NULL NULL Some10 <== don't want this one


But I don't want any rows where Table1 doesn't have data from Table2 AND Table3. I only want rows where Table1 has data in Table2 or Table3, or both. In other words, I don't want the last row.

My tables and query are extremely simplified from my real problem. I can't simply add a where clause that checks if Table2FK and Table3FK are not NULL.

In fact, the real problem has about 5 tables in the Table2 "path" and 3 in the Table3 path. And the where is checking for conditions along those paths.

Also in reality, I am using Hibernate so I must use non-database dependent functions only and I can't use UNION.

I've tried playing with INNER, RIGHT, LEFT, and FULL joins but I can't seem to get what I want. Yet it feels like I should be able to do this. If there is any way this can be done, or if it's not possible, please let me know so I can stop banging my head against the wall.

And please go easy on me, I'm not a database person, just a software developer who needs to get some data.

Sorry the table info looks so sloppy, the post took out all my nice spacing which made it more readable.

Thanks in advance for any help.