How to join Columns from two table to get my expected result?

• poornima.s_pdi

  SSCrazy

  Points: 2720

  More actions

  January 26, 2017 at 9:52 pm

  #321103

  Hi,

  Below is my code,
  

  
---- If the test table already exists, drop it
  IF OBJECT_ID('TempDB..#points','U') IS NOT NULL
   DROP TABLE #points

  create table #points(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,course_id nvarchar(max))

  --===== All Inserts into the IDENTITY column
  SET IDENTITY_INSERT #points ON

  --===== Insert the test data into the test table
INSERT INTO #points
   (ID,course_id)
SELECT '8971','45' UNION ALL
SELECT '9431','56' UNION ALL
SELECT '9352','73' UNION ALL
SELECT '9453','74' UNION ALL
SELECT '27333','57' UNION ALL
SELECT '9014','18' UNION ALL
SELECT '9015','65' UNION ALL
SELECT '5226','69' UNION ALL
SELECT '9026','51' UNION ALL
SELECT '9027','10' UNION ALL
SELECT '9455','73' UNION ALL
SELECT '9456','74' UNION ALL
SELECT '9457','74' UNION ALL
SELECT '9383','74' UNION ALL
SELECT '9384','74' UNION ALL
SELECT '9387','74' UNION ALL
SELECT '9388','73' UNION ALL
SELECT '9422','73' UNION ALL
SELECT '9461','74' UNION ALL
SELECT '9462','73' UNION ALL
SELECT '9463','73' UNION ALL
SELECT '9464','74'

  --===== Set the identity insert back to normal
  SET IDENTITY_INSERT #points ON

      --===== If the test table already exists, drop it
  IF OBJECT_ID('TempDB..#products_sub_category_details','U') IS NOT NULL
   DROP TABLE #products_sub_category_details

           CREATE TABLE #products_sub_category_details
   (
   sub_category_id INT,
   sub_category_details_id int not null PRIMARY KEY CLUSTERED)

  insert into #products_sub_category_details(sub_category_id,sub_category_details_id)
SELECT '1','31' UNION ALL
SELECT '0','300' UNION ALL
SELECT '46','46' UNION ALL
SELECT '18','10' UNION ALL
SELECT '2','2' UNION ALL
SELECT '48','48' UNION ALL
SELECT '19','11' UNION ALL
SELECT '4','45' UNION ALL
SELECT '335','74' UNION ALL
SELECT '0','367' UNION ALL
SELECT '319','394' UNION ALL
SELECT '113','152' UNION ALL
SELECT '114','153' UNION ALL
SELECT '126','69' UNION ALL
SELECT '127','187'

  select distinct(course_id) from #points

  select distinct(sub_category_details_id) from #products_sub_category_details

  
  My output is,
  
course_id    sub_category_details_id
10                2
18                10
45                11
51                31
56                45
57                46
65                48
69                69
73                74
74                152
                  153
                  187
                  300
                  367
                  394

  But My expected output be like this,

  
course_id    sub_category_details_id
10               10
18                0
0                 2
45               45
51                0
0                11
56                0
0                31
57                0
0                46
65                0
69               69
73                0
74               74
0                48
0                152
0                153
0                187
0                300
0                367
0                394

  Need to insert 0 for unmatched columns.
  How to achieve this?

  Thanks in Advance,
  Poornima

• DesNorton

  SSC-Insane

  Points: 24285

  More actions

  January 26, 2017 at 10:25 pm

  #1925232

  
SELECT course_id = ISNULL(p.course_id, 0), pscd.sub_category_details_id
FROM #products_sub_category_details AS pscd
LEFT JOIN #points AS p
 ON pscd.sub_category_details_id = p.course_id
GROUP BY pscd.sub_category_details_id, p.course_id
/***/ UNION ALL /***/
SELECT course_id, sub_category_details_id = 0
FROM #points AS p
LEFT JOIN #products_sub_category_details AS pscd
 ON p.course_id = pscd.sub_category_details_id
WHERE pscd.sub_category_details_id IS NULL
GROUP BY p.course_id


  ---

  How to post data/code on a forum to get the best help.

• poornima.s_pdi

  SSCrazy

  Points: 2720

  More actions

  January 30, 2017 at 8:43 pm

  #1925759

  Thanks for helping ..
  This is exactly what i want.
  Thanks
  DesNorton..

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 31, 2017 at 1:27 am

  #1925779

  DesNorton - Thursday, January 26, 2017 10:25 PM

  
SELECT course_id = ISNULL(p.course_id, 0), pscd.sub_category_details_id
FROM #products_sub_category_details AS pscd
LEFT JOIN #points AS p
 ON pscd.sub_category_details_id = p.course_id
GROUP BY pscd.sub_category_details_id, p.course_id
/***/ UNION ALL /***/
SELECT course_id, sub_category_details_id = 0
FROM #points AS p
LEFT JOIN #products_sub_category_details AS pscd
 ON p.course_id = pscd.sub_category_details_id
WHERE pscd.sub_category_details_id IS NULL
GROUP BY p.course_id


  Simpler:
  

  SELECT DISTINCT

  course_id = ISNULL(p.course_id,0),

  sub_category_details_id = ISNULL(d.sub_category_details_id,0)

  FROM #points p

  FULL OUTER JOIN #products_sub_category_details d

  ON d.sub_category_details_id = p.course_id

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• poornima.s_pdi

  SSCrazy

  Points: 2720

  More actions

  January 31, 2017 at 2:50 am

  #1925786

  Thanks ChrisM.
  This is more simpler and getting almost the same result