# Miscellaneous > Structured Query Language (SQL) >  Using JOIN to get desired data

## sldorman

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.

----------

