Outer Join / Union? - t / sql query

Question

I need help with a complex t / sql query for the special report I'm writing. I do not know how to formulate this problem; but i will go:

Basically, I have three tables as follows:

@PrimaryTable Key - Is unique and may or may not exist Description1 - Some field Description2 - Some field which works as a heading, more on this later @Subtable1 KeyFK - is NOT unique, links to Key in primary table. May or may not exist. May also exist multiple times per Key Description - The field I want @Subtable2 - Just like subtable1, but does not relate to subtable1 in any way 

I want to join these tables in PrimaryTable as it will look with UNION ALL, but without all NULL values.

See this query for an example:

 DECLARE @PrimaryTable AS table ("Key" int, Description1 varchar(32), Description2 varchar(32)); --Contains MAXIMUM one of each Key DECLARE @Subtable_1 AS table ("KeyPK" int, SubDescription1 varchar(32)); --Can contain zero, one or more lines with same KeyPK DECLARE @Subtable_2 AS table ("KeyPK" int, SubDescription2 varchar(32)); --Can contain zero, one or more lines with same KeyPK INSERT INTO @PrimaryTable VALUES (1, 'Description', 'Heading'); INSERT INTO @PrimaryTable VALUES (2, 'Description', 'Heading'); INSERT INTO @PrimaryTable VALUES (3, 'Description', 'Heading'); INSERT INTO @PrimaryTable VALUES (5, 'Description', 'Heading'); INSERT INTO @PrimaryTable VALUES (6, 'Description', 'Heading'); INSERT INTO @PrimaryTable VALUES (7, 'Description', 'Heading'); INSERT INTO @PrimaryTable VALUES (8, 'Description', 'Heading'); INSERT INTO @Subtable_1 VALUES (1, 'Subdescription1_1'); INSERT INTO @Subtable_2 VALUES (2, 'Subdescription2_1'); INSERT INTO @Subtable_1 VALUES (3, 'Subdescription1_1'); INSERT INTO @Subtable_2 VALUES (3, 'Subdescription2_1'); INSERT INTO @Subtable_1 VALUES (4, 'Subdescription1_1'); INSERT INTO @Subtable_2 VALUES (4, 'Subdescription2_1'); INSERT INTO @Subtable_2 VALUES (4, 'Subdescription2_2'); INSERT INTO @Subtable_1 VALUES (5, 'Subdescription1_1'); INSERT INTO @Subtable_1 VALUES (5, 'Subdescription1_2'); INSERT INTO @Subtable_2 VALUES (5, 'Subdescription2_1'); INSERT INTO @Subtable_1 VALUES (6, 'Subdescription1_1'); INSERT INTO @Subtable_2 VALUES (6, 'Subdescription2_1'); INSERT INTO @Subtable_2 VALUES (6, 'Subdescription2_2'); INSERT INTO @Subtable_1 VALUES (7, 'Subdescription1_1'); INSERT INTO @Subtable_1 VALUES (7, 'Subdescription1_2'); 

I want the result to look like this:

 /* Key Description1 Description2 Subdescription1 Subdescription2 _________________________________________________________________________ 1 Description Heading '' '' 1 Description '' Subdescription1_1 NULL 2 Description Heading '' '' 2 Description '' NULL Subdescription2_1 3 Description Heading '' '' 3 Description '' Subdescription1_1 Subdescription2_1 5 Description Heading '' '' 5 Description '' Subdescription1_1 Subdescription2_1 5 Description '' Subdescription1_2 NULL 6 Description Heading '' '' 6 Description '' Subdescription1_1 Subdescription2_1 6 Description '' NULL Subdescription2_2 7 Description Heading '' '' 7 Description '' Subdescription1_1 NULL 7 Description '' Subdescription1_2 NULL 8 Description Heading '' '' */ 

A title line with empty descriptions is not a problem; can be easily done by joining later, but I don’t know how to join these tables like this: Does anyone know how to do this?

Edit: I had to explain the report from the beginning: I am working on a very large DB structure, but I have a very strange reporting requirement.

PrimaryTable is the actual table on which the report queries Subtable1 - this is the table that explains the different parts of the primary table, this is real information. Subtable2 is not a table, but a contract (field from PrimaryTable), broken into one row pr. line.

The report then requests all the information from the primary table as a heading that includes some subtotals. Then there is the bend option, which opens in one line pr. subtable2 description / line in the contract. The number of lines pr Key simply shows the length of each fold.

As much as possible, SQL will be used to answer the answer question. - It will be a stored procedure that will generate a table with quick answers based on decisions

Answer 1

I think you need something like this:

 SELECT key, description1, description2, '', '' FROM PrimaryTable UNION SELECT distinct p.description1, '', s1.subdescription1, s2.subdescription2 FROM PrimaryTable as p LEFT JOIN Subtable1 as s1 ON p.key = s1.key LEFT JOIN Subtable2 as s2 ON p.key = s2.key 

I will also say that it looks like you have a rather strange database structure. Is there any reason why you have two subtree tables?

Answer 2

This is close to the expected results (double check where Key = 6 as well as 8):

 SELECT "Key", P1.Description1, '' AS Description2, S1.SubDescription1, S2.SubDescription2 FROM @PrimaryTable AS P1 LEFT OUTER JOIN @Subtable_1 AS S1 ON P1."Key" = S1."KeyPK" LEFT OUTER JOIN @Subtable_2 AS S2 ON P1."Key" = S2."KeyPK" UNION SELECT "Key", P1.Description1, P1.Description2, '' AS SubDescription1, '' AS SubDescription2 FROM @PrimaryTable AS P1 ORDER BY "Key", Description2 DESC;