I want to select information from three SQL tables within one query.

An example could be the following setup.

tblFriends

   id | idmother | dayBirth
  --------------------------
    1 |        1 | 09/09/21 
    2 |        2 | 09/09/21 
    3 |        3 | 11/09/21 
    4 |        3 | 11/09/21 
    5 |        4 | 07/09/21
  ... |      ... |      ...

tblMothers

   id |     name 
  --------------- 
    1 |    Alice
    2 | Samantha
    3 | Veronica 
    4 |    Maria
  ... |      ...

tblIsAssignedParty

   idMother | codeParty | price
  ------------------------------
          1 |       231 |    15
          2 |       645 |    28
          3 |       164 |    33
        ... |       ... |   ...

I want to have a query that gives me the following:

  dayBirth |   weekDay | totalFriendsForParty | totalFriendsForPartyPercent | totalFriendsNoParty | totalFriendsNoPartyPercent 
  -----------------------------------------------------------------------------------------------------------------------------
  07/09/21 |   Tuesday |                    0 |                           0 |                   1 |                       0.??    
  09/09/21 |  Thursday |                    2 |                        0.?? |                   0 |                          0  
  11/09/21 |  Saturday |                    2 |                        0.?? |                   0 |                          0

Note:

• dayBirth = simply the day of birth; I need the friends grouped by this date
• weekDay = dayBirth name
• totalFriendsForParty = friends who will be attending the party; we know if the mother has a party assigned
• totalFriendsForPartyPercent = Percentatge of friends, of the total number of friends who will attend the parties
• totalFriendsNoParty = friends who will not attend the party; we know if the mother does not have a party assigned
• totalFriendsNoPartyPercent = Percentatge of friends, of the total number of friends who will not attend the parties

I need the number of friends based on whether their mothers are at a party or not. I tried to multiple select statements in Single query but the following code didn't work:

SELECT 
(SELECT distinct dayBirth, TO_CHAR(dayBirth, 'DAY') from tblFriends) as firstSecondColumn,

(SELECT dayBirth, count(*) from tblFriends
where idMother IN (
SELECT f.idMother 
from tblFriends f
left join tblIsAssignedParty iap
on f.idMother = iap.idMother 
where iap.codeParty is not null)
group by dayBirth) as thirdColumn,

(SELECT TRUNC(count(*) / count(thirdColumn.id) , 2) from tblFriends) as quarterColumn,

(SELECT dayBirth, count(*) from tblFriends
where idMother IN (
SELECT f.idMother 
from tblFriends f
left join tblIsAssignedParty iap
on f.idMother = iap.idMother 
where iap.codeParty is not null)
group by dayBirth) as fifthColumn,

(SELECT TRUNC(count(*) / count(fifthColumn.id) , 2) from tblFriends) as  sixthColumn,

order by dayBirth

Any advice on this one? I try to learn, I do what I can :-(

Edit: I can't add inserts because it's a file upload, but I can add an approximation of table creation.

Create tables:

  CREATE TABLE tblFriends
   (    
    id NUMBER(*,0),
    idMother CHAR(10 CHAR),  
     CONSTRAINT PK_FRIEND PRIMARY KEY (id, idMother), 
     CONSTRAINT FK_IDMOTHER FOREIGN KEY (idMother)
      REFERENCES tblMothers (id),
    dayBirth DATE CONSTRAINT NN_DAY NOT NULL
   )


  CREATE TABLE tblMothers
   (    
    id CHAR(10 CHAR) CONSTRAINT PK_MOTHER PRIMARY KEY (id),   
    name VARCHAR2(20 CHAR) CONSTRAINT NN_MNAME NOT NULL
   )


  CREATE TABLE tblIsAssignedParty
   (    
    idMother CHAR(10 CHAR), 
    codeParty CHAR(10 CHAR),
    CONSTRAINT PK_ASSIGNED PRIMARY KEY (idMother, codeParty),  
    CONSTRAINT FK_ASSIGNEDMOTHER FOREIGN KEY (idMother)
      REFERENCES tblMothers (id),
    CONSTRAINT FK_ASSIGNEDPARTY FOREIGN KEY (codeParty)
      REFERENCES tblParties (codeParty),
    price DECIMAL(10,2)
   )

CodePudding user response：

You appear to want to LEFT JOIN the firends and party tables and then use conditional aggregation:

SELECT dayBirth,
       TO_CHAR(dayBirth, 'FMDAY', 'NLS_DATE_LANGUAGE=English') AS day,
       COUNT(p.idmother)
         AS totalFriendsForParty,
       COUNT(p.idmother) / COUNT(*) * 100
         AS totalFriendsForPartyPercent,
       COUNT(CASE WHEN p.idmother IS NULL THEN 1 END) AS totalFriendsNoParty,
       COUNT(CASE WHEN p.idmother IS NULL THEN 1 END) / COUNT(*) * 100
         AS totalFriendsNoPartyPercent 
FROM   tblFriends f
       LEFT OUTER JOIN tblIsAssignedParty p
       ON (f.idmother = p.idmother)
GROUP BY dayBirth

Which, for the sample data:

CREATE TABLE tblFriends (id, idmother, dayBirth) AS
SELECT 1, 1, DATE '2021-09-09' FROM DUAL UNION ALL
SELECT 2, 2, DATE '2021-09-09' FROM DUAL UNION ALL
SELECT 3, 3, DATE '2021-09-11' FROM DUAL UNION ALL
SELECT 4, 3, DATE '2021-09-11' FROM DUAL UNION ALL
SELECT 5, 4, DATE '2021-09-07' FROM DUAL;

CREATE TABLE tblIsAssignedParty (idMother, codeParty, price) AS
SELECT 1, 231, 15 FROM DUAL UNION ALL
SELECT 2, 645, 28 FROM DUAL UNION ALL
SELECT 3, 164, 33 FROM DUAL;

Outputs:

DAYBIRTH	DAY	TOTALFRIENDSFORPARTY	TOTALFRIENDSFORPARTYPERCENT	TOTALFRIENDSNOPARTY	TOTALFRIENDSNOPARTYPERCENT
09-SEP-21	THURSDAY	2	100	0	0
11-SEP-21	SATURDAY	2	100	0	0
07-SEP-21	TUESDAY	0	0	1	100

db<>fiddle here

CodePudding user response：

We have use some join concept to interact with the information from 3 tables.

Customer1 table

Customer2 table

Product table

Example syntax to select from multiple tables:

SELECT p. p_id, p.cus_id, p.p_name, c1.name1, c2.name2  
FROM product AS p  
LEFT JOIN customer1 AS c1  
ON p.cus_id=c1.cus_id  
LEFT JOIN customer2 AS c2  
ON p.cus_id = c2.cus_id

with this simple example we can extract details from multiple tables.