t-sql help

I have the following 3 test tables and look for the result as ID only show once in the result table.:

TEST1_TB:            

ID            TEST1_NUM
--------   ---------  
1111         3
1112       1
1113       15
1114         6


TEST2_TB:
ID            TEST2_NUM
--------   ---------
1112       4
1114       2
1115       6
1116       20
1118         2




TEST3_TB:
ID             TEST3_NUM
--------    ---------
1111            1
1119            5


THE RESULT
ID            TEST1_NUM        TEST2_NUM      TEST3_NUM
-------      --------                ----------         ---------      
1111             3                         0                    1
1112             1                        4                     0
1113             15                      0                     0
1114             6                        2                     0
1115             6                        0                     0
1116             20                      0                     0
1118             2                         0                    0
1119             0                         0                    5

thank you
jfreeman2010Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ephraim WangoyaCommented:

select ID ,           TEST1_NUM ,       TEST2_NUM ,     TEST3_NUM
from TEST1_TB
inner join TEST1_TB on TEST1_TB.ID = TEST2_TB.ID
inner join TEST3_TB on TEST3_TB.ID = TEST2_TB.ID
Ephraim WangoyaCommented:

Sorry, disregard that, i misunderstood your question
jfreeman2010Author Commented:
the inner join only show the all match row.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Ephraim WangoyaCommented:

Use left outer join instead

select ID ,  TEST1_NUM ,  TEST2_NUM ,  TEST3_NUM
from TEST1_TB
left join TEST1_TB on TEST1_TB.ID = TEST2_TB.ID
left join TEST3_TB on TEST3_TB.ID = TEST2_TB.ID
jfreeman2010Author Commented:
the left join will missing the TEST2_TB records that not match TEST1_TB.

Sorry, my result table should be as following:
THE RESULT
ID            TEST1_NUM        TEST2_NUM      TEST3_NUM
-------      --------                ----------         ---------      
1111             3                         0                    1
1112             1                        4                     0
1113             15                      0                     0
1114             6                        2                     0
1115             0                        6                     0
1116             0                      20                     0
1118             0                         2                    0
1119             0                         0                    5
Paul_Harris_FusionCommented:
Select IDS.ID
, COALESCE (T1.TEST1_NUM,0) as TEST1
, COALESCE (T2.TEST2_NUM,0) as TEST2
, COALESCE (T3.TEST3_NUM,0) as TEST3
FROM
(
select TEST1_TB.ID
UNION DISTINCT TEST2_TB.ID
UNION DISTINCT TEST3_TB.ID
) IDS
LEFT OUTER JOIN TEST1_TB T1 on T1.ID = IIDS.ID
LEFT OUTER JOIN TEST2_TB T2 on T2.ID = IIDS.ID
LEFT OUTER JOIN TEST3_TB T3 on T3.ID = IIDS.ID
jfreeman2010Author Commented:
Hi Paul Harris Fusion ,

Thank you very much for the responded.  I am getting a error in the distinct, also, the left outer join, should it be like this:
LEFT OUTER JOIN TEST1_TB T1 on T1.ID = IDS.ID
LEFT OUTER JOIN TEST2_TB T2 on T2.ID = IDS.ID
LEFT OUTER JOIN TEST3_TB T3 on T3.ID = IDS.ID

IDS or IIDS?

thanks,
jfreeman2010Author Commented:
This works for the example result:

CREATE TABLE #TEST1_TB(
      ID                  INT,
      TEST1_NUM      INT
)

CREATE TABLE #TEST2_TB(
      ID                  INT,
      TEST2_NUM      INT
)

CREATE TABLE #TEST3_TB(
      ID                  INT,
      TEST3_NUM      INT
)


INSERT INTO #TEST1_TB ( ID, TEST1_NUM ) VALUES  ( 1111,3)
INSERT INTO #TEST1_TB ( ID, TEST1_NUM ) VALUES  ( 1112,1)
INSERT INTO #TEST1_TB ( ID, TEST1_NUM ) VALUES  ( 1113,15)
INSERT INTO #TEST1_TB ( ID, TEST1_NUM ) VALUES  ( 1114,6)

INSERT INTO #TEST2_TB ( ID, TEST2_NUM ) VALUES  ( 1112,4)
INSERT INTO #TEST2_TB ( ID, TEST2_NUM ) VALUES  ( 1114,2)
INSERT INTO #TEST2_TB ( ID, TEST2_NUM ) VALUES  ( 1115,6)
INSERT INTO #TEST2_TB ( ID, TEST2_NUM ) VALUES  ( 1116,20)
INSERT INTO #TEST2_TB ( ID, TEST2_NUM ) VALUES  ( 1118,2)

INSERT INTO #TEST3_TB ( ID, TEST3_NUM ) VALUES  ( 1111,1)
INSERT INTO #TEST3_TB ( ID, TEST3_NUM ) VALUES  ( 1119,5)
 
 
Select IDS.ID
, COALESCE (T1.TEST1_NUM,0) as TEST1
, COALESCE (T2.TEST2_NUM,0) as TEST2
, COALESCE (T3.TEST3_NUM,0) as TEST3
FROM
(
select ID
FROM #TEST1_TB
UNION
SELECT ID
FROM #TEST2_TB
UNION
SELECT ID
FROM #TEST3_TB
) IDS
LEFT OUTER JOIN #TEST1_TB T1 on T1.ID = IDS.ID
LEFT OUTER JOIN #TEST2_TB T2 on T2.ID = IDS.ID
LEFT OUTER JOIN #TEST3_TB T3 on T3.ID = IDS.ID
SharathData EngineerCommented:
try FULL JOIN.
SELECT COALESCE(t1.ID,t2.ID,t3.ID) ID, 
       COALESCE(t1.TEST1_NUM,0)    TEST1_NUM, 
       COALESCE(t2.TEST2_NUM,0)    TEST2_NUM, 
       COALESCE(t3.TEST3_NUM,0)    TEST3_NUM 
  FROM #TEST1_TB t1 
       FULL JOIN #TEST2_TB t2 
         ON t1.ID = t2.ID 
       FULL JOIN #TEST3_TB t3 
         ON t1.ID = t3.ID 
             OR t2.ID = t3.ID

Open in new window

SharathData EngineerCommented:
There are no number data for 1115,1116,1118,1119 in TEST1_TB. Do you want TEST2_TB numbers to be displayed under TEST1_NUM in the result set? And TEST3_TB numbers under TEST3_NUM only?
jfreeman2010Author Commented:
TEST2_TB should be under TEST2_TB.  I updated this info, please see above update.  

Sharath 123:

so if I have move then 3 tables, I just need to add on the columns and FULL JOIN all of them.  I do have a 5 tables join at work.
SharathData EngineerCommented:
Ok, I overlooked the post http:#35318130 Did you try my post. You need to FULL JOIN the other two tables also. Let me know if you have trouble in doing that.
jfreeman2010Author Commented:
I try the 4 tables join and have problems at the end, it added 2 more rows with null ID

CREATE TABLE #TEST1_TB(
      ID                  INT,
      TEST1_NUM      INT
)

CREATE TABLE #TEST2_TB(
      ID                  INT,
      TEST2_NUM      INT
)

CREATE TABLE #TEST3_TB(
      ID                  INT,
      TEST3_NUM      INT
)

CREATE TABLE #TEST4_TB(
      ID                  INT,
      TEST4_NUM      INT
)

INSERT INTO #TEST1_TB ( ID, TEST1_NUM ) VALUES  ( 1111,3)
INSERT INTO #TEST1_TB ( ID, TEST1_NUM ) VALUES  ( 1112,1)
INSERT INTO #TEST1_TB ( ID, TEST1_NUM ) VALUES  ( 1113,15)
INSERT INTO #TEST1_TB ( ID, TEST1_NUM ) VALUES  ( 1114,6)

INSERT INTO #TEST2_TB ( ID, TEST2_NUM ) VALUES  ( 1112,4)
INSERT INTO #TEST2_TB ( ID, TEST2_NUM ) VALUES  ( 1114,2)
INSERT INTO #TEST2_TB ( ID, TEST2_NUM ) VALUES  ( 1115,6)
INSERT INTO #TEST2_TB ( ID, TEST2_NUM ) VALUES  ( 1116,20)
INSERT INTO #TEST2_TB ( ID, TEST2_NUM ) VALUES  ( 1118,2)

INSERT INTO #TEST3_TB ( ID, TEST3_NUM ) VALUES  ( 1111,1)
INSERT INTO #TEST3_TB ( ID, TEST3_NUM ) VALUES  ( 1119,5)
 
INSERT INTO #TEST4_TB ( ID, TEST4_NUM ) VALUES  ( 1117,2)
INSERT INTO #TEST4_TB ( ID, TEST4_NUM ) VALUES  ( 1119,1)
INSERT INTO #TEST4_TB ( ID, TEST4_NUM ) VALUES  ( 1120,1)




 
SELECT COALESCE(t1.ID,t2.ID,t3.ID) ID,
       COALESCE(t1.TEST1_NUM,0)    TEST1_NUM,
       COALESCE(t2.TEST2_NUM,0)    TEST2_NUM,
       COALESCE(t3.TEST3_NUM,0)    TEST3_NUM,
       COALESCE(t4.TEST4_NUM,0)    TEST4_NUM  
  FROM #TEST1_TB t1
       FULL JOIN #TEST2_TB t2
         ON t1.ID = t2.ID
       FULL JOIN #TEST3_TB t3
         ON t1.ID = t3.ID
             OR t2.ID = t3.ID
       FULL JOIN #TEST4_TB t4
         ON t1.ID = t4.ID
                   OR t2.ID = t4.ID
                   or t3.ID = t4.ID
                  
DROP TABLE #TEST1_TB;
DROP TABLE #TEST2_TB;
DROP TABLE #TEST3_TB;
DROP TABLE #TEST4_TB;

SharathData EngineerCommented:
try this.
select coalesce(t1.ID,t2.ID,t3.ID,t4.ID) ID,
       coalesce(t1.TEST1_NUM,0) TEST1_NUM,
       coalesce(t2.TEST2_NUM,0) TEST2_NUM,
       coalesce(t3.TEST3_NUM,0) TEST3_NUM,
       coalesce(t4.TEST4_NUM,0) TEST4_NUM
  from #TEST1_TB t1
  full join #TEST2_TB t2 on t1.ID = t2.ID
  full join #TEST3_TB t3 on coalesce(t1.ID,t2.ID) = t3.ID
  full join #TEST4_TB t4 on coalesce(t1.ID,t2.ID,t3.ID) = t4.ID
 order by ID

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfreeman2010Author Commented:
Sharath 123.

Yes, it works, I also try with 5 tables join and also works.  Thank you very much for your help!!!!!
jfreeman2010Author Commented:
it works, THANK YOU!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.