SQL Server : Join

Question

Hi All,

First two column is from one set of query statements and the remaining from other set. I want to display the values in a single row. Can anybody help me

A      B      C      D

NULL   NULL   0      22

0      699    NULL   NULL

SELECT statement:

SELECT P.A, P.B, T2.C, T2.D 
FROM Table1 AS P 
JOIN (
    SELECT MAX(ID) ID, COUNT(ID) AS A 
    FROM Table1 
    WHERE Type = 0 
    GROUP BY Type) AS A1 
  ON A1.ParcelID = P.ID 
RIGHT OUTER JOIN (
    SELECT MAX(ID) ID, COUNT(ID) AS B 
    FROM Table1 
    WHERE Type = 1 GROUP BY Type) AS B1 
  ON B1.ID = P.ID 
FULL JOIN (
    SELECT R.ID,ISNULL(C1.C,0) C, ISNULL(D1.D,0) D 
    FROM Table2 AS R 
    FULL JOIN (
        SELECT MAX(ID) ID, COUNT(ID) AS C 
        FROM Table2 
        WHERE Type = 0 
        GROUP BY Type) AS C1 
      ON C1.ID = R.ID 
    RIGHT OUTER JOIN (
        SELECT MAX(ID) ID, COUNT(ID) AS D 
        FROM Table2 
        WHERE Type = 1 
        GROUP BY Type) AS D1 
      ON D1.ID = R.ID) AS T2 
  ON T2.ID = P.ID

Answer 1

Hi, if you are a little more specific about what you i can help you better but i will try it any way. If i understand you you can do like this

Select A, B, NULL AS c, NULL AS D FROM query 1

UNION ALL

Select NULL AS A, NULL AS B, c, D FROM query 2

Best Regards, Iordan

Answer 2

You can do this with a UNION ALL. Just use your tables instead of the @TableVariables I declared in the sample below.

SET NOCOUNT ON

DECLARE @Temp1 TABLE 
(
    DataColumn1 VarChar (100),
    DataColumn2 VarChar (100),
    DataColumn3 VarChar (100),
    DataColumn4 VarChar (100)
)

DECLARE @Temp2 TABLE 
(
    DataColumn1 VarChar (100),
    DataColumn2 VarChar (100),
    DataColumn3 VarChar (100),
    DataColumn4 VarChar (100)
)

insert into @Temp1 Values (NULL, NULL, 0, 22)
insert into @Temp1 Values (NULL, NULL, 0, 23)
insert into @Temp1 Values (NULL, NULL, 0, 24)
insert into @Temp2 Values (0, 697, NULL, NULL)
insert into @Temp2 Values (0, 698, NULL, NULL)
insert into @Temp2 Values (0, 699, NULL, NULL)

SELECT 
    DataColumn1, 
    DataColumn2, 
    DataColumn3, 
    DataColumn4
FROM @Temp1

UNION ALL

SELECT 
    DataColumn1, 
    DataColumn2, 
    DataColumn3, 
    DataColumn4
FROM @Temp2

Answer 3

If you have a common field as indicated by your comment, then use that with an inner join:

SELECT A, B, C, D FROM Table1 INNER JOIN Table2 ON (Table1.CommonField = Table2.CommonField)

This will yield a result with all four columns in one row. This assumes that CommonField is a candidate key in at least one table. You may need to adjust the INNER JOIN to be a LEFT JOIN depending on whether you need all fields from one table if the other table's CommonField is NULL. Also, if you need all rows from both tables regardless of whether there is a matching row in the corresponding table, you will need to use a LEFT JOIN and write the query twice (once with Table1 joining Table2, and once with Table 2 joining Table1) and UNION them together -- similar to the other answers, only taking into account the common field.

Edit: What about this:

SELECT A1.A, B1.B, T2.C, T2.D 
FROM    (
      SELECT MAX(ID) ID, COUNT(ID) AS A 
      FROM Table1 WHERE Type = 0 GROUP BY Type
     ) AS A1 ON A1.ID = P.ID 
    FULL OUTER JOIN (
       SELECT MAX(ID) ID, COUNT(ID) AS B 
       FROM Table1 WHERE Type = 1 GROUP BY Type
      ) AS B1 ON B1.ID = A1.ID 
    FULL OUTER JOIN (
       SELECT MAX(ID) ID, COUNT(ID) AS C 
       FROM Table2 WHERE Type = 0 GROUP BY Type
      ) AS C1 ON C1.ID = A1.ID 
    FULL OUTER JOIN (
       SELECT MAX(ID) ID, COUNT(ID) AS D 
       FROM Table2 WHERE Type = 1 GROUP BY Type
      ) AS D1 ON D1.ID = A1.ID