CODEWITHSUNDEEP
sql-server
JTR
JTR

Reputation: 333

How to set select as null or nothing when join from two tables?

This is my first table1:

ID | Name1 | Name2 | Name3
---+-------+-------+------
1  |  aaa  |  bbb  |  ccc
2  |  aab  |  bbc  |  cca
3  |  abb  |  bcc  |  caa

And this is my another table2:

t1ID | NameT1
-----+-------
1    |  eee
1    |  fff
1    |  ggg
2    |  hhh
3    |  iii
3    |  jjj

I want to join both tables like this

ID | Name1 | Name2 | Name3 | NameT1
---+-------+-------+-------+--------
1  |  aaa  |  bbb  |  ccc  |  eee
1  |       |       |       |  fff
1  |       |       |       |  ggg
2  |  aab  |  bbc  |  cca  |  hhh
3  |  abb  |  bcc  |  caa  |  iii
3  |       |       |       |  jjj

I've try using INNER JOIN and UNION ALL but it's give me this result

ID | Name1 | Name2 | Name3 | NameT1
---+-------+-------+-------+--------
1  |  aaa  |  bbb  |  ccc  |  eee
1  |  aaa  |  bbb  |  ccc  |  fff
1  |  aaa  |  bbb  |  ccc  |  ggg
2  |  aab  |  bbc  |  cca  |  hhh
3  |  abb  |  bcc  |  caa  |  iii
3  |  abb  |  bcc  |  caa  |  jjj

So, Any suggestion for this?

Upvotes: 3

Views: 47

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: You can achieve this result by using CTE and window function as below:

CREATE TABLE #tableA(ID INT, Name1 VARCHAR(50), Name2 VARCHAR(50), Name3 VARCHAR(50))
INSERT INTO #tableA VALUES
(1,'aaa','bbb','ccc'),
(2,'aab','bbc','cca'),
(3,'abb','bcc','caa')

CREATE TABLE #tableB(t1ID INT, NameT1 VARCHAR(50))
INSERT INTO #tableB VALUES
(1,'eee'),
(1,'fff'),
(1,'ggg'),
(2,'hhh'),
(3,'iii'),
(3,'jjj')

;WITH tabB AS (
    SELECT *,
        RANK() OVER(PARTITION BY t1ID ORDER BY NameT1) rnk
    FROM #tableB B),

    tabA AS (
    SELECT *,
        RANK() OVER(PARTITION BY ID ORDER BY Name1) rnk
    FROM #tableA A)

SELECT B.t1ID, 
    ISNULL(A.Name1, '') AS Name1, 
    ISNULL(A.Name2, '') AS Name2, 
    ISNULL(A.Name3, '') AS Name3, 
    B.NameT1
FROM tabB B
LEFT JOIN tabA A ON A.rnk = B.rnk AND A.ID = B.t1ID
ORDER BY B.t1ID

OUTPUT:

t1ID    Name1   Name2   Name3   NameT1
1       aaa     bbb     ccc     eee
1                               fff
1                               ggg
2       aab     bbc     cca     hhh
3       abb     bcc     caa     iii
3                               jjj

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try this

;WITH CTE
AS
(
    SELECT
      RN = ROW_NUMBER() OVER(PARTITION BY T1.[ID] ORDER BY T1.[ID]),
      ID = T1.[ID],
      T1.Name1,
      T1.Name2,
      T1.Name3,
      T2.NameT1
      FROM T1
        INNER JOIN T2
            ON T1.ID = T2.t1ID
)
SELECT
  ID,
  Name1 = CASE RN WHEN 1 THEN Name1 END,
  Name2 = CASE RN WHEN 1 THEN Name2 END,
  Name3 = CASE RN WHEN 1 THEN Name3 END,
  NameT1
  FROM CTE

Please find the SQL Fiddle Here

http://sqlfiddle.com/#!18/305ec/4

Upvotes: 2

Related Questions