SQLTeam.com | Weblogs | Forums

Join question - sorry, my brain has stopped working :-(


#1
DECLARE @incoming AS TABLE
(
CustID VARCHAR (50),
TDate VARCHAR (6),
Value DECIMAL (15, 2)
)

DECLARE @outgoing AS TABLE
(
CustID VARCHAR (50),
TDate VARCHAR (6),
Value DECIMAL (15, 2)
)

INSERT INTO @incoming
VALUES 
(
'1', '201701', '10.24'
)
INSERT INTO @incoming
VALUES 
(
'2', '201701', '35.66'
) 
INSERT INTO @incoming
VALUES 
(
'3', '201702', '19.55'
) 

INSERT INTO @outgoing
VALUES 
(
'3', '201701', '10.24'
)
INSERT INTO @outgoing
VALUES 
(
'4', '201701', '35.66'
) 
INSERT INTO @outgoing
VALUES 
(
'5', '201702', '19.55'
)

SELECT * FROM @incoming
UNION ALL
SELECT * FROM @outgoing

The output I'm looking for is six columns, three from @incoming and three from @outgoing containing all records (irrespective of NULLs).

The common column in each table is CustID.

The output would be

1 201701 10.24 NULL NULL NULL
2 201701 35.66 NULL NULL NULL
3 201702 19.55 3 201701 10.24
NULL NULL NULL 4 201701 35.66
NULL NULL NULL 5 201702 19.55

Thanks in advance.


#2
SELECT i.CustID AS Inc_CustID,i.TDate AS Inc_TDate,i.Value AS Inc_Value , NULL AS Outg_CustID, NULL AS Outg_TDate, NULL AS Outg_Value FROM @incoming as I
UNION ALL
SELECT NULL AS Inc_CustID,NULL AS Inc_TDate,NULL AS Inc_Value , o.CustID AS Outg_CustID, o.TDate AS Outg_TDate, o.Value AS Outg_Value FROM @outgoing as O

Output of if:

Inc_CustID	Inc_TDate	Inc_Value	Outg_CustID	Outg_TDate	Outg_Value
1	201701	10.24	null	null	null
2	201701	35.66	null	null	null
3	201702	19.55	null	null	null
null	null	null	3	201701	10.24
null	null	null	4	201701	35.66
null	null	null	5	201702	19.55

I didn't add an order by ; you can add on what field you like