CODEWITHSUNDEEP
sqlsql-serverjoin
Sai
Sai

Reputation: 1117

joining two tables and result data should contain non duplicate items in sql?

i have two tables like below

student1 table:

+---+--------+-----+
|SID|SSection|SRank|
+---+--------+-----+
|  a|       1|    1|
|  b|       2|    2|
|  d|       4|    2|
|  e|       4|    1|
|  c|       3|    4|
+---+--------+-----+

student 2 table:

+---+--------+-----+
|SID|SSection|SRank|
+---+--------+-----+
|  a|       2|    1|
|  b|       2|    3|
|  f|       4|    2|
|  e|       4|    1|
|  c|       3|    4|
+---+--------+-----+

i want to write a select query and it should give result like below:

+---+--------+----------+----------+
|SID|SSection|test1SRank|test2SRank|
+---+--------+----------+----------+
|  f|       4|         0|         2|
|  e|       4|         1|         1|
|  d|       4|         2|         0|
|  c|       3|         4|         4|
|  b|       2|         2|         3|
|  a|       1|         1|         0|
|  a|       2|         0|         1|
+---+--------+----------+----------+

Upvotes: 0

Views: 57

Answers (3)

Eralper
Eralper

Reputation: 6622

A simpler way of the same query is as follows producing the same output with the second query

;with cte as (

    select
        SID, SSection, SRank as test1SRank, null as test2SRank
    from student1 s1

    union all

    select
        SID, SSection, null as test1SRank, SRank as test2SRank
    from student2 s2

)
select
    SID, SSection, 
    isnull(max(test1SRank),0) test1SRank, 
    isnull(max(test2SRank),0) test2SRank
from cte
group by 
    SID, SSection

Could you please try following SQL CTE expression with DENSE_RANK() function

;with cte as (

    select
        SID, SSection, SRank as test1SRank, null as test2SRank
    from student1 s1

    union all

    select
        SID, SSection, null as test1SRank, SRank as test2SRank
    from student2 s2

), cte2 as (
select
*,
DENSE_RANK() over (partition by SID order by SSection) as rn
from cte
)
--order by SID
select
    SID, SSection, 
    isnull(max(test1SRank),0) test1SRank, 
    isnull(max(test2SRank),0) test2SRank
from cte2
group by 
    SID, SSection, rn

Upvotes: 0

gofr1
gofr1

Reputation: 15997

Another way with UNION asn CTE:

;WITH cte AS (
    SELECT  [SID],
            SSection,
            SRank as test1SRank,
            0 as test2SRank
    FROM student1 
    UNION ALL
    SELECT  [SID],
            SSection,
            0 as test1SRank,
            SRank as test2SRank
    FROM student2
)

SELECT  [SID],
        SSection,
        MAX(test1SRank) as test1SRank,
        MAX(test2SRank) as test2SRank
FROM cte
GROUP BY [SID],
        SSection

Output:

SID  SSection    test1SRank  test2SRank
---- ----------- ----------- -----------
a    1           1           0
a    2           0           1
b    2           2           3
c    3           4           4
d    4           2           0
e    4           1           1
f    4           0           2

Upvotes: 1

MatBailie
MatBailie

Reputation: 86775

SELECT
  COALESCE(student1.SID     , student2.SID     )   AS SID,
  COALESCE(student1.SSection, student2.SSection)   AS SSection,
  COALESCE(student1.SRank,    0                )   AS test1SRank,
  COALESCE(student2.SRank,    0                )   AS test2SRank  
FROM
  student1
FULL OUTER JOIN
  student2
    ON  student2.SID      = student1.SID
    AND student2.SSection = student1.SSection
ORDER BY
  1, 2

http://sqlfiddle.com/#!18/0a986/3

Upvotes: 2

Related Questions