I am using SQL Server and I have 3 tables.
Students in a school, each one of these students has a hobby and each one of these students has a preferred colour too.
I want a table that shows each one of these student with their hobbies and preferred colours in the same table, where hobbies and colours are next to each other and not duplicated.
Students
StID Name
---------
101 Mike
102 Nancy
103 Tom
104 Lisa
105 John
106 Matt
Hobbies
StID Hobby PracticeDate
-------------------------
101 Bikes 12/2/2024
101 Music 24/2/2024
101 Movies 14/3/2024
102 Art 13/2/2024
102 Music 23/2/2024
103 Soccer 21/3/2024
103 Drawing 04/4/2024
103 Movies 22/2/2024
105 Music 11/2/2024
105 Bikes 26/3/2024
Colours
StID Colour PaintingDate
--------------------------
101 Blue 30/5/2024
101 Green 01/4/2024
102 Yellow 20/4/2024
102 Black 18/3/2024
102 Green 29/2/2024
103 Black 26/3/2024
103 Yellow 11/2/2024
103 Red 30/3/2024
104 Blue 07/2/2024
104 Pink 24/2/2024
I want the result to be like this
StID Name Hobby PracticeDate Colour PaintingDate
-----------------------------------------------------
101 Mike Bikes 12/2/2024 Blue 30/5/2024
101 Mike Music 24/2/2024 Green 01/4/2024
101 Mike Movies 14/3/2024
102 Nancy Art 13/2/2024 Yellow 20/4/2024
102 Nancy Music 23/2/2024 Black 18/3/2024
102 Nancy Green 29/2/2024
103 Tom Soccer 21/3/2024 Black 26/3/2024
103 Tom Drawing 04/4/2024 Yellow 11/2/2024
103 Tom Movies 22/2/2024 Red 30/3/2024
104 Lisa Blue 07/2/2024
104 Lisa Pink 24/2/2024
105 John Music 11/2/2024
105 John Bikes 26/3/2024
106 Matt
I tried joining the tables but that did not work as expected.
SELECT
p.StID,
p.Name,
ph.Hobby,
ph.PracticeDate,
lr.Colour,
lr.PaintingDate
FROM
Students p
LEFT JOIN
Hobbies ph ON p.StID = ph.StID
LEFT JOIN
Colours lr ON p.StID = lr.StID
Code to create tables and insert records
-- Create Students table
CREATE TABLE Students (
StID INT PRIMARY KEY,
Name VARCHAR(50)
);
-- Insert data into Students table
INSERT INTO Students (StID, Name) VALUES
(101, 'Mike'),
(102, 'Nancy'),
(103, 'Tom'),
(104, 'Lisa'),
(105, 'John'),
(106, 'Matt');
-- Create Hobbies table
CREATE TABLE Hobbies (
StID INT,
Hobby VARCHAR(50),
PracticeDate DATE,
FOREIGN KEY (StID) REFERENCES Students(StID)
);
-- Insert data into Hobbies table
INSERT INTO Hobbies (StID, Hobby, PracticeDate) VALUES
(101, 'Bikes', '2024-02-12'),
(101, 'Music', '2024-02-24'),
(101, 'Movies', '2024-03-14'),
(102, 'Art', '2024-02-13'),
(102, 'Music', '2024-02-23'),
(103, 'Soccer', '2024-03-21'),
(103, 'Drawing', '2024-04-04'),
(103, 'Movies', '2024-02-22'),
(105, 'Music', '2024-02-11'),
(105, 'Bikes', '2024-03-26');
-- Create Colours table
CREATE TABLE Colours (
StID INT,
Colour VARCHAR(50),
PaintingDate DATE,
FOREIGN KEY (StID) REFERENCES Students(StID)
);
-- Insert data into Colours table
INSERT INTO Colours (StID, Colour, PaintingDate) VALUES
(101, 'Blue', '2024-05-30'),
(101, 'Green', '2024-04-01'),
(102, 'Yellow', '2024-04-20'),
(102, 'Black', '2024-03-18'),
(102, 'Green', '2024-02-29'),
(103, 'Black', '2024-03-26'),
(103, 'Yellow', '2024-02-11'),
(103, 'Red', '2024-03-30'),
(104, 'Blue', '2024-02-07'),
(104, 'Pink', '2024-02-24');
This is an odd request but the query isn't all that complicated. Here is one solution. I am using a pair of ctes to join the Students table with the respective interest. Then simply a full outer join between those two will return the results you are after.
with HobbyList as
(
select s.StID
, s.Name
, h.Hobby
, h.PracticeDate
, RowNum = ROW_NUMBER() over(partition by s.StID order by h.PracticeDate)
from Students s
left join Hobbies h on h.StID = s.StID
)
, ColourList as
(
select s.StID
, s.Name
, c.Colour
, c.PaintingDate
, RowNum = ROW_NUMBER() over(partition by s.StID order by c.PaintingDate)
from Students s
left join Colours c on c.StID = s.StID
)
select StID = coalesce(h.StID, c.StId)
, Name = coalesce(h.Name, c.Name)
, Hobby = h.Hobby
, PracticeDate = h.PracticeDate
, Colour = c.Colour
, PaintingDate = c.PaintingDate
from HobbyList h
full outer join ColourList c on c.StID = h.StID and c.RowNum = h.RowNum
order by coalesce(h.StID, c.StId)