Query to display one to many result in a single table-CodePudding

Ive been trying to use the GROUP function and also PIVOT but I cannot wrap my head around how to merge these tables and combine duplicate rows. Currently my SELECT statement returns results with duplicate UserID rows but I want to consolidate them into columns.

How would I join TABLE1 and TABLE2 into a new table which would look something like this:

NEW TABLE:
UserID    Username  ParentID 1   ParentID 2
--------- --------  --------     ----------
1         Dave      1             2
2         Sally     3             4



TABLE1:
UserID    Username  ParentID
--------- --------  --------
1         Dave      1
1         Dave      2
2         Sally     3
2         Sally     4


Table 2:
ParentID  Username
--------- --------
1         Sarah
2         Joe
3         Tom
4         Mark

CodePudding user response：

This is mySql ver 5.6. Create a concatenated ParentID using group concat then separate the concatenated ParentID (1,2) and (3,4) into ParentID 1 and Parent ID 2.

SELECT t1.UserID, 
       t1.Username,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(t1.ParentID), ',', 1), ',', -1) AS `ParentID 1`,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(t1.ParentID), ',', 2), ',', -1) as `ParentID 2`
FROM TABLE1 t1
INNER JOIN TABLE2 t2 on t1.ParentID = t2.ParentID
GROUP BY t1.UserID
ORDER BY t1.UserID;


Result:
UserID  Username    ParentID 1  ParentID 2
1       Dave        1           2
2       Sally       3           4

CodePudding user response：

O r a c l e
The with clause is here just to generate some sample data and, as such, it is not a part of the answer.
After joining the tables you can use LAST_VALUE analytic function with windowing clause to get the next PARENT_ID of the user. That column (PARENT_ID_2) contains a value only within the first row of a particular USER_ID (ROW_NUMBER analytic function). Afterwords just filter out rows where PARENT_ID_2 is empty...
Sample data:

WITH 
    tbl_1 AS
        (
            Select 1 "USER_ID", 'Dave' "USER_NAME", 1 "PARENT_ID" From Dual Union All
            Select 1 "USER_ID", 'Dave' "USER_NAME", 2 "PARENT_ID" From Dual Union All
            Select 2 "USER_ID", 'Sally' "USER_NAME", 3 "PARENT_ID" From Dual Union All
            Select 2 "USER_ID", 'Sally' "USER_NAME", 4 "PARENT_ID" From Dual 
        ),
    tbl_2 AS
        (
            Select  1 "PARENT_ID", 'Sarah' "USER_NAME" From Dual Union All
            Select  2 "PARENT_ID", 'Joe' "USER_NAME" From Dual Union All
            Select  3 "PARENT_ID", 'Tom' "USER_NAME" From Dual Union All
            Select  4 "PARENT_ID", 'Mark' "USER_NAME" From Dual 
        )

Main SQL:

SELECT 
    *
FROM  (
          SELECT  
                  t1.USER_ID "USER_ID", 
                  t1.USER_NAME "USER_NAME",
                  t1.PARENT_ID "PARENT_ID_1",
                  CASE  
                      WHEN ROW_NUMBER() OVER(PARTITION BY t1.USER_ID ORDER BY t1.USER_ID) = 1
                      THEN LAST_VALUE(t1.PARENT_ID) OVER(PARTITION BY t1.USER_ID ORDER BY t1.USER_ID ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
                  END "PARENT_ID_2"
          FROM 
              tbl_1 t1
          INNER JOIN 
              tbl_2 t2 ON(t1.PARENT_ID = t2.PARENT_ID)
      )
WHERE PARENT_ID_2 Is Not Null

... and the Result ...

--     USER_ID USER_NAME PARENT_ID_1 PARENT_ID_2
--  ---------- --------- ----------- -----------
--           1 Dave                1           2 
--           2 Sally               3           4

The windowing clause in this answer

ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING

takes curent and next row and returns the value defined by the analytic function (LAST_VALUE) taking care of grouping (PARTITION BY) and ordering of the rows. Regards...