FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE User_Warranty 
(
    User_ID INT NOT NULL
    , Serial VARCHAR(50) NOT NULL
    , Warranty DATE NOT NULL
    , PRIMARY KEY (User_ID, Serial)
);
CREATE TABLE User_Serial 
(
    ID INT NOT NULL
    , Name VARCHAR(50) NOT NULL
    , Side VARCHAR(10) NOT NULL
    , Serial VARCHAR(50) NOT NULL
    , PRIMARY KEY (ID, Serial)
);

INSERT INTO User_Warranty (User_ID, Serial, Warranty) VALUES (1, '12', '2024-01-01');
INSERT INTO User_Warranty (User_ID, Serial, Warranty) VALUES (4, '13423', '2022-11-21');
INSERT INTO User_Warranty (User_ID, Serial, Warranty) VALUES (1, '643r', '2026-05-13');
INSERT INTO User_Warranty (User_ID, Serial, Warranty) VALUES (4, 'r42r4', '2023-10-07');
INSERT INTO User_Warranty (User_ID, Serial, Warranty) VALUES (5, '743f', '2026-06-22');
INSERT INTO User_Warranty (User_ID, Serial, Warranty) VALUES (6, '4324', '2027-04-15');

INSERT INTO User_Serial (ID, Name, Side, Serial) VALUES (1, 'Bob', 'Left', '12');
INSERT INTO User_Serial (ID, Name, Side, Serial) VALUES (4, 'Mark', 'Right', '13423');
INSERT INTO User_Serial (ID, Name, Side, Serial) VALUES (1, 'Bob', 'Right', '643r');
INSERT INTO User_Serial (ID, Name, Side, Serial) VALUES (4, 'Mark', 'Left', 'r42r4');
INSERT INTO User_Serial (ID, Name, Side, Serial) VALUES (5, 'Ella', 'Left', '743f');
INSERT INTO User_Serial (ID, Name, Side, Serial) VALUES (6, 'Deb', 'Right', '4324');

SELECT 
    us.ID AS User_ID
    , us.Name
    , MAX(CASE WHEN us.Side = 'Left' THEN uw.Warranty END) `Left Warranty`
    , MAX(CASE WHEN us.Side = 'Right' THEN uw.Warranty END) `Right Warranty`
    , MAX(CASE WHEN us.Side = 'Left' THEN us.Serial END) `Left Serial`
    , MAX(CASE WHEN us.Side = 'Right' THEN us.Serial END) `Right Serial`
FROM User_Serial us
JOIN User_Warranty uw ON us.ID = uw.User_ID AND us.Serial = uw.Serial
GROUP BY us.ID, us.Name;

User_ID	Name	Left Warranty	Right Warranty	Left Serial	Right Serial
1	Bob	2024-01-01	2026-05-13	12	643r
4	Mark	2023-10-07	2022-11-21	r42r4	13423
5	Ella	2026-06-22	NULL	743f	NULL
6	Deb	NULL	2027-04-15	NULL	4324