Oracle sql combine table and do counting

Question

How to count the total project participation (it means the project count) for the project Name Lakers and participate in Client meeting between 12/25-27/2022 for all employee display with the UserAc and their total PJCount？

Also, if the employee UserAc do not appear in the ProjectParticipate. It also need to display their UserAc and the PJCount is 0 in the final output when they are exists in the EMPLOYEE Table.

Table EMPLOYEE：

UserAc
Ken1
John1
Alex1
Dan1

Table ProjectParticipate

PJName    UserAc    PJRn    PDate        PJCount
Lakers    Ken1      0200    12/25/2022   5.3
Lakers    Ken1      0200    12/28/2022   8.7
Lakers    Ken1      0200    12/27/2022   4.3
Bulls     Ken1      0200    12/25/2022   4
Lakers    John1     0100    12/25/2022   6
Lakers    Alex1     0200    12/25/2022   3

Table ProjectGuide

PJName    PJNumber    NumberExplan
Lakers    0100        Phone call
Lakers    0200        Client meeting
Bulls     0100        Phone call
Bulls     0200        Team meeting
State     0100        Discussion
State     0200        Documentation

Here is the current query I develop

Select 
    UserAc,
    COUNT(PJCount) 
from
    ProjectParticipate
Where 
    PJRn = ‘0200’ and PDate BETWEEN 12/25/2022 and 12/27/2022
--it seems to need to use TABLE ProjectGuide to know Project:Lakers, PJNumber:0200 = Client meeting instead of just PJRn = 0200
Group by 
    UserAc

I don’t know how to use and combine 3 tables to fulfill the requirements.

Below is the expected results

UserAc      PJCount
Ken1        9.6
John1       0
Alex1       3
Dan1        0

Thank you so much for your help.

Answer 1

You can use left join to get PJCount of only 'Client meeting' and PDate between 25th and 27th December. Instead of COUNT() you need to use conditional SUM().

Schema and insert statement:

 CREATE TABLE EMPLOYEE(UserAc VARCHAR(50));

 INSERT INTO EMPLOYEE VALUES('Ken1');
 INSERT INTO EMPLOYEE VALUES('John1');
 INSERT INTO EMPLOYEE VALUES('Alex1');
 INSERT INTO EMPLOYEE VALUES('Dan1');

 CREATE TABLE ProjectParticipate(PJName VARCHAR(50), UserAc VARCHAR(50), PJRn VARCHAR(50), PDate DATE, PJCount FLOAT);

 INSERT INTO ProjectParticipate VALUES('Lakers',    'Ken1',      '0200',   DATE'2022-12-25',   5.3);
 INSERT INTO ProjectParticipate VALUES('Lakers',    'Ken1',      '0200',    DATE'2022-12-28',   8.7);
 INSERT INTO ProjectParticipate VALUES('Lakers',    'Ken1',      '0200',    DATE'2022-12-27',   4.3);
 INSERT INTO ProjectParticipate VALUES('Bulls',     'Ken1',      '0200',    DATE'2022-12-25',   4);
 INSERT INTO ProjectParticipate VALUES('Lakers',    'John1',     '0100',    DATE'2022-12-25',   6);
 INSERT INTO ProjectParticipate VALUES('Lakers',    'Alex1',     '0200',    DATE'2022-12-25',   3);

 CREATE Table ProjectGuide(PJName VARCHAR(50),    PJNumber    VARCHAR(50), NumberExplan VARCHAR(50))

 INSERT INTO ProjectGuide VALUES('Lakers','0100','Phone call');
 INSERT INTO ProjectGuide VALUES('Lakers','0200','Client meeting');
 INSERT INTO ProjectGuide VALUES('Bulls','0100','Phone call');
 INSERT INTO ProjectGuide VALUES('Bulls','0200','Team meeting');
 INSERT INTO ProjectGuide VALUES('State','0100','Discussion');
 INSERT INTO ProjectGuide VALUES('State','0200','Documentation');

Query:

 Select 
     E.UserAc,
     SUM(case when PJNumber is not null then PP.PJCount else 0 end ) PJCount
 from
     EMPLOYEE E LEFT JOIN ProjectParticipate PP ON E.UserAc=PP.UserAc
     and PP.PDate BETWEEN DATE'2022-12-25' and DATE'2022-12-27'
     LEFT JOIN ProjectGuide PG ON PP.PJName=PG.PJName AND PP.PJRn=PG.PJNumber 
     and PG.NumberExplan = 'Client meeting'
 
 Group by 
     E.UserAc

Output

USERAC	PJCOUNT
Ken1	9.6
Alex1	3
John1	0
Dan1	0

db<>fiddle here