Mysql - How to group 2 table records with added column details

chriscavage
July 18, 2024
205 views
2 votes
3 Answers

I have a mysql table that looks like this:

table1

User_ID  |  Serial |  Warranty 
1        |  12     | 2024-01-01
4        |  13423  | 2022-11-21
1        |  643r   | 2026-05-13
4        |  r42r4  | 2023-10-07
5        |  743f   | 2026-06-22
6        |  4324   | 2027-04-15

table2

ID |  Name  |  Side  |  Serial
1  | Bob    |  Left  |  12     
4  | Mark   |  Right |  13423  
1  | Bob    |  Right |  643r   
4  | Mark   |  Left  |  r42r4  
5  | Ella   |  Left  |  743f   
6  | Deb    |  Right |  4324

I need a query that will combine all the details into one result like this, for instance:

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

I know how to group, but I’m stuck completely on the logic to combine these table into a central query result with all the combined record information based on the serial numbers.

Tags: mysql sql

Answers

Not clear what table1 is bringing to the party, as dougp said.

But simple conditional aggregation would work for the sample data in table2. The general term for this is "pivoting".

CREATE TABLE table_2 (
    ID INT,
    Name VARCHAR(50),
    Side VARCHAR(5),
    Warranty DATE,
    Serial VARCHAR(20)
);

INSERT INTO table_2 (ID, Name, Side, Warranty, Serial) VALUES
    (1, 'Bob', 'Left', '2024-01-01', '12'),
    (4, 'Mark', 'Right', '2022-11-21', '13423'),
    (1, 'Bob', 'Right', '2026-05-13', '643r'),
    (4, 'Mark', 'Left', '2023-10-07', 'r42r4'),
    (5, 'Ella', 'Left', '2026-06-22', '743f'),
    (6, 'Deb', 'Right', '2027-04-15', '4324');

select id,
       name,
       max(case when side = 'Left' then warranty else null end) as left_warranty,
       max(case when side = 'Right' then warranty else null end) as right_warranty,
       max(case when side = 'Left' then serial else null end) as left_serial,
       max(case when side = 'Right' then serial else null end) as right_serial
  from table_2
 group
    by id,
       name

A join will do the trick.

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
LEFT JOIN User_Warranty uw ON us.ID = uw.User_ID AND us.Serial = uw.Serial
GROUP BY us.ID, us.Name;

You need an OUTER JOIN and define the join on two columns (User_ID and Serial), use a CASE statement to manually pivot the data, then aggregate somehow (I use MAX() below) to consolidate the results to eliminate duplicate rows that contain NULLs.

WITH
table1 (
    User_ID
  , Serial
  , Warranty 
) as (
        select 1, '12'   , DATE '2024-01-01'
  union select 4, '13423', DATE '2022-11-21'
  union select 1, '643r' , DATE '2026-05-13'
  union select 4, 'r42r4', DATE '2023-10-07'
  union select 5, '743f' , DATE '2026-06-22'
  union select 6, '4324' , DATE '2027-04-15'
), 
table2 (
    ID
  , Name
  , Side
  , Serial
) as (
        select 1, 'Bob' , 'Left' , '12'
  union select 4, 'Mark', 'Right', '13423'
  union select 1, 'Bob' , 'Right', '643r'
  union select 4, 'Mark', 'Left' , 'r42r4'
  union select 5, 'Ella', 'Left' , '743f'
  union select 6, 'Deb' , 'Right', '4324'
)

select
  table2.ID as User_ID
, table2.Name
, max(case when table2.Side = 'Left'  then table1.Warranty end) as "Left Warranty"
, max(case when table2.Side = 'Right' then table1.Warranty end) as "Right Warranty"
, max(case when table2.Side = 'Left'  then table1.Serial   end) as "Left Serial"
, max(case when table2.Side = 'Right' then table1.Serial   end) as "Right Serial"

from table2
  left outer join table1 on table1.User_ID = table2.ID
                        and table1.Serial = table2.Serial

group by 
  table2.ID
, table2.Name

order by 1

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – How to group 2 table records with added column details

Answers