CODEWITHSUNDEEP
sqlsql-serverjoinduplicates
Ibrahim
Ibrahim

Reputation: 1289

SQL - Join 2 Different Tables By Name Without Duplicates

I'm having trouble querying from 2 different tables: A and B. Each table represents the score results of a class. Tables A and B represent different tests. Some students have taken both tests and some have only taken one.

Here is a short version of what I'm working with:

CREATE TABLE A
(
fn varchar(50),
ln varchar(50),
score1 int
);

CREATE TABLE B
(
fn varchar(50),
ln varchar(50),
score2 int
);

INSERT INTO A (fn, ln, score)
VALUES ('abe','farm',90);
INSERT INTO A (fn, ln, score)
VALUES ('carly','lina',70);
INSERT INTO A (fn, ln, score)
VALUES ('bobby','echo',40);
INSERT INTO A (fn, ln, score)
VALUES ('joe','robin',11);

INSERT INTO B (fn, ln, score)
VALUES ('abe','farm',95);
INSERT INTO B (fn, ln, score)
VALUES ('carly','lina',75);
INSERT INTO B (fn, ln, score)
VALUES ('geb','lina',100);
INSERT INTO B (fn, ln, score)
VALUES ('bobby','echo',40);

I want to get a resulting data table that represents every student (without duplicates) that NULLs out the non-existing scores and matches first and last names accordingly for each student:

/===========================================\
| fn       | ln       | score1   | score2   |
|===========================================|
| abe      | farm     | 90       | 95       |
| bobby    | echo     | 40       | 40       |
| carly    | lina     | 70       | 75       |
| geb      | lina     | NULL     | 100      |
| joe      | robin    | 11       | NULL     |
\===========================================/

I am working in Microsoft SQL Server.

Upvotes: 1

Views: 99

Answers (5)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

You can go either with a UNION ALL or FULL OUTER JOIN.

    ;WITH cte_1
     As (       SELECT fn, ln, score as score1, NULL as score2
   FROM  A
   UNION ALL
   SELECT fn, ln, NULL, score as score2
   FROM B)
     SELECT fn,ln,Max(score1) score1,Max(score2) score2
    FROM cte_1
    GROUP BY fn,ln

Or

    SELECT ISNULL(a.fn, b.fn) fn, 
                   ISNULL(a.ln, b.ln) ln, 
                     a.score score1, b.score score2 
     FROM A
        FULL JOIN B ON A.fn = B.fn and A.ln = B.ln

Upvotes: 0

JPil
JPil

Reputation: 301

Try this 

SELECT COALESCE(a.fn, b.fn) fn, 
   COALESCE(a.ln, b.ln) ln, 
   a.score score1, b.score score2  
FROM a FULL JOIN b ON b.fn = a.fn AND b.ln = a.ln 
ORDER BY fn

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can try query like this: To get your result set full join will not help

SELECT
  a.fn,
  a.ln,
  a.score1,
  b.score2
FROM a
LEFT JOIN b
  ON a.fn = b.fn
  AND a.ln = b.ln
UNION
SELECT
  b.fn,
  b.ln,
  a.score1,
  b.score2
FROM b
LEFT JOIN a
  ON a.fn = b.fn
  AND a.ln = b.ln
WHERE a.fn IS NULL
AND a.ln IS NULL

Upvotes: 0

M T Head
M T Head

Reputation: 1290

select FName
      ,LName
      ,Score1 = sum(Score1)
      ,Score2 = sum(Score2)
from
(
select 
    FName = A.Fn
   ,LName = A.Ln 
   ,Score1 = A.Score1
   ,Score2 = 0 
from #A A
union all 
select 
    FName = B.Fn
   ,LName = B.Ln 
   ,Score1 = 0
   ,Score2 = B.Score2 
from #B B
) x
Group by Fname, LName

Upvotes: 0

Matt
Matt

Reputation: 14381

SELECT
    t.fn
    ,t.ln
    ,MAX(t.score1) as score1
    ,MAX(t.score2) as score2
FROM
    (
       SELECT fn, ln, score as score1, NULL as score2
       FROM
          A
       UNION ALL
       SELECT fn, ln, NULL, score2
       FROM
          B
    ) t
GROUP BY
    t.fn, t.ln

Upvotes: 2

Related Questions