Oracle Connect the rows in different table and show it in one column-CodePudding

How can I get the results as below by combine those tables?

The expected results is

PDate        ProjectGDetails   ProjectTDetails   PJCount   Notes
01/25/2022   A B Good              B B Good         3       Yes
01/27/2022   D B Bad               C Good           2       No
01/25/2022   A Good                D D Bad          1       No

I need to get the data only for the user Ken1 with the project Lakers in January 2022. And connect the data of the PJGrade1, ProjectGrade2, Remarks with a ‘ ’ in the tables of ProjectGrade and ProjectTier. If some data in PJGrade1, ProjectGrade2, Remarks is null, cannot have a addition ‘ ’ in the display such as A Good.

Table ProjectDetails

PJName    UserAc    PJRn    PDate         PJCount    Notes
Lakers    Ken1      0201    01/25/2022       3        Yes
Lakers    Ken1      0202    12/28/2022       5        No
Lakers    Ken1      0203    01/27/2022       2        Yes
Bulls     Ken1      0201    01/25/2022       4        Yes
Lakers    John1     0101    01/25/2022       7        No
Lakers    Alex1     0201    05/25/2022       2        Yes
Lakers    Ken1      0204    01/25/2022       1        No

Table ProjectGrade

PJName    PJNumber    PJDetail         PJGrade1 PJGrade2  Remark
Lakers    0201        Client meetings     A         B      Good
Lakers    0101        Phone call          C         C       Bad
Lakers    0202        Client meeting      B         C       Bad
Lakers    0203        Client meeting      D         B       Bad
Lakers    0204        Client meeting                A       Good
Bulls     0201        Phone call.         A         B       Good
Bulls     0202        Team meeting        C         C       Bad
State     0101        Discussion          B         A       Good
State     0201        Documentation       B         B       Good

Table ProjectTier

PJName    PJNumber    PJDetail         PJGrade1 PJGrade2   Remark 
Lakers    0201        Client meetings     B         B       Good
Lakers    0101        Phone call          F         C       Bad
Lakers    0202        Client meeting      B         B       Good
Lakers    0203        Client meeting      C                 Good
Lakers    0204        Client meeting      D         D       Bad
Bulls     0201        Phone call.         A         A       Good
Bulls     0202        Team meeting        B         A       Good
State     0101        Discussion          C         B       Good
State     0201        Documentation       C         F       Bad

Here is the current query I have

Select 
    UserAc,
    PDate,
    PJCount,
    Notes
    PJGrade1   ‘ ’, PJGrade2   ‘ ’ , Remarks As ProjectGDetails

from
    ProjectDetails, ProjectGrade, ProjectTier
Where 
    PJName = ‘Lakers’ and PDate BETWEEN DATE ‘01/01/2022, and DATE ‘01/31/2022’

May I know how to combine those tables and get the expected results

Thank you so much for your help

CodePudding user response：

Use:

You have no filter for UserAC = 'Ken1';
' not ‘ for string literals;
|| and not for the string concatenation operator;
date literals are in the format DATE 'YYYY-MM-DD' and not DATE ‘MM/DD/YYYY‘; and
add join conditions rather than using a CROSS JOIN (via the legacy comma-join syntax)

Something like:

SELECT d.PDate,
       g.PJGrade1 || ' ' || g.PJGrade2 || ' ' || g.Remark As ProjectGDetails,
       t.PJGrade1 || ' ' || t.PJGrade2 || ' ' || t.Remark As ProjectTDetails,
       d.PJCount,
       d.Notes
FROM   ProjectDetails d
       INNER JOIN ProjectGrade g
       ON (d.PJName = g.PJName AND d.PJRn = g.PJNumber)
       INNER JOIN ProjectTier t
       ON (d.PJName = t.PJName AND d.PJRn = t.PJNumber)
WHERE  d.PJName =  'Lakers'
AND    d.UserAC =  'Ken1'
AND    d.PDate  >= DATE '2022-01-01'
AND    d.PDate  <  DATE '2022-02-01'

CodePudding user response：

Maybe this is what you need.
The WITH clause is just to create sample data and those cte-s are not part of the answer.

Collect combined data from grade and tier (in the subquery) and join them with project. Subquery can be changed, if needed, for some additional control or conditioning. You can exclude collumns you don't need from the main SELECT and you can change the where clause to see different data. Hopefully this will solve your problem. Regards...

WITH
    ProjectDetails AS
        (
            SELECT  'Lakers' "PJNAME",  'Ken1'  "USERAC",   '0201' "PJRN",  To_Date('01/25/2022', 'mm/dd/yyyy') "PDATE",    3 "PJCOUNT",    'Yes' "NOTES"   FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  'Ken1'  "USERAC",   '0202' "PJRN",  To_Date('12/28/2022', 'mm/dd/yyyy') "PDATE",    5 "PJCOUNT",    'No'  "NOTES"   FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  'Ken1'  "USERAC",   '0203' "PJRN",  To_Date('01/27/2022', 'mm/dd/yyyy') "PDATE",    2 "PJCOUNT",    'Yes' "NOTES"   FROM DUAL UNION ALL
            SELECT  'Bulls'  "PJNAME",  'Ken1'  "USERAC",   '0201' "PJRN",  To_Date('01/25/2022', 'mm/dd/yyyy') "PDATE",    4 "PJCOUNT",    'Yes' "NOTES"   FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  'John1' "USERAC",   '0101' "PJRN",  To_Date('01/25/2022', 'mm/dd/yyyy') "PDATE",    7 "PJCOUNT",    'No'  "NOTES"   FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  'Alex1' "USERAC",   '0201' "PJRN",  To_Date('05/25/2022', 'mm/dd/yyyy') "PDATE",    2 "PJCOUNT",    'Yes' "NOTES"   FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  'Ken1'  "USERAC",   '0204' "PJRN",  To_Date('01/25/2022', 'mm/dd/yyyy') "PDATE",    1 "PJCOUNT",    'No'  "NOTES"   FROM DUAL 
        ),
    ProjectGrade AS
        (
            SELECT  'Lakers' "PJNAME",  '0201'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'A'  "PJGRADE1",    'B' "PJGRADE2",     'Good' "REMARK"     FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  '0101'  "PJNUMBER",     'Phone call'      "PJDETAIL",   'C'  "PJGRADE1",    'C' "PJGRADE2",     'Bad'  "REMARK"     FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  '0202'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'B'  "PJGRADE1",    'C' "PJGRADE2",     'Bad'  "REMARK"     FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  '0203'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'D'  "PJGRADE1",    'B' "PJGRADE2",     'Bad'  "REMARK"     FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  '0204'  "PJNUMBER",     'Client meetings' "PJDETAIL",   Null "PJGRADE1",    'A' "PJGRADE2",     'Good' "REMARK"     FROM DUAL UNION ALL
            SELECT  'Bulls'  "PJNAME",  '0201'  "PJNUMBER",     'Phone call'      "PJDETAIL",   'A'  "PJGRADE1",    'B' "PJGRADE2",     'Good' "REMARK"     FROM DUAL UNION ALL
            SELECT  'Bulls'  "PJNAME",  '0202'  "PJNUMBER",     'Team meeting'    "PJDETAIL",   'B'  "PJGRADE1",    'C' "PJGRADE2",     'Bad'  "REMARK"     FROM DUAL UNION ALL
            SELECT  'State'  "PJNAME",  '0101'  "PJNUMBER",     'Discussion'      "PJDETAIL",   'A'  "PJGRADE1",    'A' "PJGRADE2",     'Good' "REMARK"     FROM DUAL UNION ALL
            SELECT  'State'  "PJNAME",  '0201'  "PJNUMBER",     'Documentation'   "PJDETAIL",   'A'  "PJGRADE1",    'B' "PJGRADE2",     'Good' "REMARK"     FROM DUAL 
        ),
    ProjectTier AS
        (
            SELECT  'Lakers' "PJNAME",  '0201'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'B'  "PJGRADE1",    'B'  "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  '0101'  "PJNUMBER",     'Phone call'      "PJDETAIL",   'F'  "PJGRADE1",    'C'  "PJGRADE2",    'Bad'  "REMARK"     FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  '0202'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'B'  "PJGRADE1",    'B'  "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  '0203'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'C'  "PJGRADE1",    Null "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
            SELECT  'Lakers' "PJNAME",  '0204'  "PJNUMBER",     'Client meetings' "PJDETAIL",   'D'  "PJGRADE1",    'D'  "PJGRADE2",    'Bad'  "REMARK"     FROM DUAL UNION ALL
            SELECT  'Bulls'  "PJNAME",  '0201'  "PJNUMBER",     'Phone call'      "PJDETAIL",   'A'  "PJGRADE1",    'A'  "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
            SELECT  'Bulls'  "PJNAME",  '0202'  "PJNUMBER",     'Team meeting'    "PJDETAIL",   'B'  "PJGRADE1",    'A'  "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
            SELECT  'State'  "PJNAME",  '0101'  "PJNUMBER",     'Discussion'      "PJDETAIL",   'C'  "PJGRADE1",    'B'  "PJGRADE2",    'Good' "REMARK"     FROM DUAL UNION ALL
            SELECT  'State'  "PJNAME",  '0201'  "PJNUMBER",     'Documentation'   "PJDETAIL",   'C'  "PJGRADE1",    'F'  "PJGRADE2",    'Bad'  "REMARK"     FROM DUAL 
        )
--  ********************************************************************************************
SELECT 
    p.PJRN "PJRN",
    p.PJNAME "PJNAME",
    p.PDATE "PDATE",
    REPLACE(LTRIM(gt.PJ_G1 || ' ' || gt.PJ_G2 || ' ' || gt.PJ_G_REM, ' '), '  ', ' ') "GRADES", --LTRIM removes plus sign from begining and Replace changes two plus signs with one if that happens because of null values
    REPLACE(LTRIM(gt.PJ_T1 || ' ' || gt.PJ_T2 || ' ' || gt.PJ_T_REM, ' '), '  ', ' ') "TIER", 
    p.PJCOUNT "PJ_COUNT",
    p.NOTES "PJ_NOTES",
    p.USERAC "USERAC"
FROM 
    ProjectDetails p
INNER JOIN          -- collect data from grade and tier combined
    (
        SELECT
            g.PJNAME "PJNAME",
            g.PJNUMBER "PJRN",
            g.PJGRADE1 "PJ_G1",
            g.PJGRADE2 "PJ_G2",
            g.REMARK "PJ_G_REM",
            t.PJGRADE1 "PJ_T1",
            t.PJGRADE2 "PJ_T2",
            t.REMARK "PJ_T_REM"
        FROM
            ProjectGrade g
        INNER JOIN
            ProjectTier t ON(t.PJNUMBER = g.PJNUMBER)    
    ) gt ON(p.PJNAME = gt.PJNAME And p.PJRN = gt.PJRN)
WHERE               -- you can changge the where clause to whatever suites you the best
    p.PJNAME =  'Lakers' AND      
    p.PDATE  Between To_Date('01/01/2022', 'mm/dd/yyyy') AND To_Date('01/31/2022', 'mm/dd/yyyy') 
ORDER BY
    p.PDATE, p.PJRN
--  
--  R e s u l t
--  
--  PJRN PJNAME PDATE     GRADES   TIER       PJ_COUNT PJ_NOTES USERAC
--  ---- ------ --------- -------- -------- ---------- -------- ------
--  0101 Lakers 25-JAN-22 C C Bad  C B Good          7 No       John1  
--  0101 Lakers 25-JAN-22 C C Bad  F C Bad           7 No       John1  
--  0201 Lakers 25-JAN-22 A B Good C F Bad           3 Yes      Ken1   
--  0201 Lakers 25-JAN-22 A B Good A A Good          3 Yes      Ken1   
--  0201 Lakers 25-JAN-22 A B Good B B Good          3 Yes      Ken1   
--  0204 Lakers 25-JAN-22 A Good   D D Bad           1 No       Ken1   
--  0203 Lakers 27-JAN-22 D B Bad  C Good            2 Yes      Ken1

I removed the leading plus sign and duplicate plus signs (if any) which could happen because of nulls in grade or tier.