EN VI

SQL to make the final result display all data (matching and not matching) from both table after joining the two table?

How to SQL to make the final result display all data (matching and not matching) from both table after joining the two table?

I have 2 table which storing Students data

Table A

Name	id	Course
Bob	1	Marketing
Dom	2	Science
Edward	3	IT

Table B

Name	id	Course
Dom	2	Mathematic
Chris	4	IT

Now I plan to display all student data as below

Name	id	Course
Bob	1	Marketing
Dom	2	Science, Mathematic
Edward	3	IT
Chris	4	IT

SELECT
CASE WHEN a.name = b.name THEN a.name ELSE a.name as Student_Name,
CASE WHEN a.id = b.id THEN a.id ELSE a.id as Student_Id,
CASE WHEN a.course = b.course THEN a.course + ',' + b.course ELSE a.course as Student_Course
FROM student a
full join student b on a.name=b.name

But with above query I only able to get the result as

Name	id	Course
Bob	1	Marketing
Dom	2	Science, Mathematic
Edward	3	IT
NULL	NULL	NULL

Which Chris become NULL in final result...How can I make it to select all? Have suffer on this whole week.

Solution:

We can take a union of the two tables and then aggregate by name:

SELECT name, id, GROUP_CONCAT(course) AS courses  -- see note below
FROM
(
    SELECT name, id, course FROM TableA
    UNION ALL
    SELECT name, id, course FROM TableB
) t
GROUP BY name, id
ORDER BY id;

Note that the above would be for MySQL. If you're really using SQL Server (2016 or later), then replace GROUP_CONCAT() with STRING_AGG().

Tags