SQL Oracle - Is there a way to use the value selected from a query to derive another value from anot-CodePudding

I know this may sound like another join relational question, but I think the major problem I have is a spaghetti code.

Following is the code that I worked with:

select fruit, COUNT(fruit) as Total
FROM transaction
where fruit = 'Apple'
GROUP BY fruit;

which will give me the value as

fruit         Total
Apple           10

and another query is

select customerPhone, count(customerPhone) as Total
    from CustomerDBTrans 
    where FruitID in (select FruitID 
        from Storage 
        where fruit = 'Apple')
    Group BY customerPhone;

which gives me the value as

CustomerPhone        Total
111111                10
222222                5
333333                7
444444                10

I need to select from here CustomerPhone 111111 and 444444 as I want to know a customer that bought 'Apple' every single time there was a transaction, and use that PhoneNumber to derive customer name from another table named CustomerDB.

I was thinking of using Outer Join, but then realized that ain't gonna work, and tried to look through over my textbook for last 4 hours.

What could be the right function to use in this kind of case?

Kinda stucked cause most of the tutorial material that I have access to only shows the case where both table has a same number of rows.

CodePudding user response：

Use a HAVING clause and conditional aggregation to count the number of non-Apples:

SELECT c.customerPhone,
       MAX(c.name) AS name,
       COUNT(c.customerPhone) AS Total
FROM   CustomerDBTrans t
       INNER JOIN CustomerDB c
       ON (t.customerPhone = c.customerPhone)
GROUP BY c.customerPhone
HAVING COUNT(
         CASE WHEN FruitID NOT IN (
                SELECT FruitID 
                FROM   Storage 
                WHERE  fruit = 'Apple'
              )
         THEN 1
         END
       ) = 0;

Or use an OUTER JOIN:

SELECT c.customerPhone,
       MAX(c.name) AS name,
       COUNT(c.customerPhone) AS Total
FROM   CustomerDBTrans t
       INNER JOIN CustomerDB c
       ON (t.customerPhone = c.customerPhone)
       LEFT OUTER JOIN storage s
       ON (    t.fruitid = s.fruitid
           AND s.fruit   = 'Apple')
GROUP BY c.customerPhone
HAVING COUNT(CASE WHEN s.fuitid IS NULL THEN 1 END) = 0;

CodePudding user response：

Try it this way...

WITH
    transaction AS
        (
            Select 1 "ID", 'Apple' "FRUIT" From Dual Union All
            Select 2 "ID", 'Apple' "FRUIT" From Dual Union All
            Select 3 "ID", 'Apple' "FRUIT" From Dual Union All
            Select 4 "ID", 'Apple' "FRUIT" From Dual Union All
            Select 5 "ID", 'Apple' "FRUIT" From Dual Union All
            Select 6 "ID", 'Apple' "FRUIT" From Dual Union All
            Select 7 "ID", 'Apple' "FRUIT" From Dual Union All
            Select 8 "ID", 'Apple' "FRUIT" From Dual Union All
            Select 9 "ID", 'Apple' "FRUIT" From Dual Union All
            Select 10 "ID", 'Apple' "FRUIT" From Dual Union All
            Select 1 "ID", 'Pear' "FRUIT" From Dual Union All
            Select 2 "ID", 'Pear' "FRUIT" From Dual Union All
            Select 3 "ID", 'Pear' "FRUIT" From Dual Union All
            Select 4 "ID", 'Pear' "FRUIT" From Dual 
        ),
    customer_trans AS
        (
            Select 111111 "CUSTOMER_PHONE", 'Apple' "FRUIT", 1 "TRANS_NO" From Dual Union All
            Select 111111 "CUSTOMER_PHONE", 'Apple' "FRUIT", 2 "TRANS_NO" From Dual Union All
            Select 111111 "CUSTOMER_PHONE", 'Apple' "FRUIT", 3 "TRANS_NO" From Dual Union All
            Select 111111 "CUSTOMER_PHONE", 'Apple' "FRUIT", 4 "TRANS_NO" From Dual Union All
            Select 111111 "CUSTOMER_PHONE", 'Apple' "FRUIT", 5 "TRANS_NO" From Dual Union All
            Select 111111 "CUSTOMER_PHONE", 'Apple' "FRUIT", 6 "TRANS_NO" From Dual Union All
            Select 111111 "CUSTOMER_PHONE", 'Apple' "FRUIT", 7 "TRANS_NO" From Dual Union All
            Select 111111 "CUSTOMER_PHONE", 'Apple' "FRUIT", 8 "TRANS_NO" From Dual Union All
            Select 111111 "CUSTOMER_PHONE", 'Apple' "FRUIT", 9 "TRANS_NO" From Dual Union All
            Select 111111 "CUSTOMER_PHONE", 'Apple' "FRUIT", 10 "TRANS_NO" From Dual Union All
            Select 222222 "CUSTOMER_PHONE", 'Apple' "FRUIT", 1 "TRANS_NO" From Dual Union All
            Select 222222 "CUSTOMER_PHONE", 'Apple' "FRUIT", 2 "TRANS_NO" From Dual Union All
            Select 222222 "CUSTOMER_PHONE", 'Apple' "FRUIT", 3 "TRANS_NO" From Dual Union All
            Select 222222 "CUSTOMER_PHONE", 'Apple' "FRUIT", 4 "TRANS_NO" From Dual Union All
            Select 222222 "CUSTOMER_PHONE", 'Apple' "FRUIT", 5 "TRANS_NO" From Dual Union All
            Select 333333 "CUSTOMER_PHONE", 'Apple' "FRUIT", 1 "TRANS_NO" From Dual Union All
            Select 333333 "CUSTOMER_PHONE", 'Apple' "FRUIT", 2 "TRANS_NO" From Dual Union All
            Select 333333 "CUSTOMER_PHONE", 'Apple' "FRUIT", 3 "TRANS_NO" From Dual Union All
            Select 333333 "CUSTOMER_PHONE", 'Apple' "FRUIT", 4 "TRANS_NO" From Dual Union All
            Select 333333 "CUSTOMER_PHONE", 'Apple' "FRUIT", 5 "TRANS_NO" From Dual Union All
            Select 333333 "CUSTOMER_PHONE", 'Apple' "FRUIT", 6 "TRANS_NO" From Dual Union All
            Select 333333 "CUSTOMER_PHONE", 'Apple' "FRUIT", 7 "TRANS_NO" From Dual Union All
            Select 444444 "CUSTOMER_PHONE", 'Apple' "FRUIT", 1 "TRANS_NO" From Dual Union All
            Select 444444 "CUSTOMER_PHONE", 'Apple' "FRUIT", 2 "TRANS_NO" From Dual Union All
            Select 444444 "CUSTOMER_PHONE", 'Apple' "FRUIT", 3 "TRANS_NO" From Dual Union All
            Select 444444 "CUSTOMER_PHONE", 'Apple' "FRUIT", 4 "TRANS_NO" From Dual Union All
            Select 444444 "CUSTOMER_PHONE", 'Apple' "FRUIT", 5 "TRANS_NO" From Dual Union All
            Select 444444 "CUSTOMER_PHONE", 'Apple' "FRUIT", 6 "TRANS_NO" From Dual Union All
            Select 444444 "CUSTOMER_PHONE", 'Apple' "FRUIT", 7 "TRANS_NO" From Dual Union All
            Select 444444 "CUSTOMER_PHONE", 'Apple' "FRUIT", 8 "TRANS_NO" From Dual Union All
            Select 444444 "CUSTOMER_PHONE", 'Apple' "FRUIT", 9 "TRANS_NO" From Dual Union All
            Select 444444 "CUSTOMER_PHONE", 'Apple' "FRUIT", 10 "TRANS_NO" From Dual
        )

WITH clause is here just to create some sample data...

SELECT
    ct.FRUIT "FRUIT",
    ct.CUSTOMER_PHONE "CUSTOMER_PHONE",
    Count(ct.CUSTOMER_PHONE) "CUSTOMER_TOTAL",
    t.TOTAL "TRANSACTION_TOTAL"
FROM
    customer_trans ct
INNER JOIN    
    (   SELECT FRUIT "FRUIT", Count(*) "TOTAL"
        FROM transaction
        GROUP BY FRUIT
    ) t ON(ct.FRUIT = t.FRUIT)
GROUP BY
    ct.FRUIT,
    ct.CUSTOMER_PHONE,
    t.TOTAL
HAVING
    Count(ct.CUSTOMER_PHONE) = t.TOTAL
/*  R e s u l t :
FRUIT CUSTOMER_PHONE CUSTOMER_TOTAL TRANSACTION_TOTAL
----- -------------- -------------- -----------------
Apple         111111             10                10 
Apple         444444             10                10
*/

This query will select you phone numbers of any customer that has been involved in each and every transaction ('Apple' or anything else). If you want to filter the data on a particular fruit you can do it later. Regards....