go
DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
go
CREATE TABLE #tabClass
(
ClassGuid uniqueIdentifier not null default newid(),
ClassName nvarchar(32) not null
);
CREATE TABLE #tabStudent
(
StudentGuid uniqueIdentifier not null default newid(),
StudentName nvarchar(32) not null,
ClassGuid uniqueIdentifier null -- Foreign key.
);
go
INSERT INTO #tabClass
(ClassGuid, ClassName)
VALUES
('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting')
;
INSERT INTO #tabStudent
(StudentName, ClassGuid)
VALUES
('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
('Betty Boot' , 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
('Betty Boot' , '98509D36-A2C8-4A65-A310-E744F5621C83'),
('Carla Cap' , null)
;
go
SELECT
c.ClassName,
s.StudentName
from
#tabClass as c
RIGHT OUTER JOIN #tabStudent as s ON s.ClassGuid = c.ClassGuid
--where
-- c.ClassName LIKE '%Math%'
order by
c.ClassName,
s.StudentName
FOR
JSON AUTO
--, INCLUDE_NULL_VALUES
;
go
DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
go