locked
PL/SQL queries to return different records RRS feed

  • Question

  • User-34860367 posted

    Hi all,

    I used the 1st query into 2nd one when counting the commission as following:

    1.
    
    SELECT DISTINCT tblCustomer.Name, tblCustomer.JobType, tblSale.Unit, tblSale.Price
    FROM tblCustomer, tblSale
    WHERE tblCustomer.id = tblSale.customerid;
    
    2. 
    
    SELECT b.Name, 
    (CASE WHEN b.JobType = "FULL TIME" THEN ((b.Unit * b.Price) * 0.25) 
    WHEN b.JobType = "PART TIME" THEN ((b.Unit * b.Price) * 0.15)
    END) AS b.Commission
    FROM (SELECT DISTINCT tblCustomer.Name, tblCustomer.JobType, tblSale.Unit, tblSale.Price
                FROM tblCustomer, tblSale
                WHERE tblCustomer.id = tblSale.customerid) b
    );

    I use the COUNT(*) for both above queries, there are more records on 2nd query. My expectation are produced same records for both queries. Do you know why and how to correct the syntax on them. Thanks.  

    Wednesday, December 9, 2015 5:55 PM

Answers

  • User269602965 posted
    Use an INNER JOIN not a FULL OUTER JOIN with DISTINCT
    
    SELECT a.Name, a.JobType, b.Unit, b.Price
    FROM tblCustomer a
    INNER JOIN tblSale b ON a.id = b.customerid;
    
    2. 
    
    SELECT
      c.Name, 
      CASE 
        WHEN c.JobType = "FULL TIME" THEN ((c.Unit * c.Price) * 0.25) 
        WHEN c.JobType = "PART TIME" THEN ((c.Unit * c.Price) * 0.15)
       END AS Commission
    FROM 
      (SELECT a.Name, a.JobType, b.Unit, b.Price
       FROM tblCustomer a
       INNER JOIN tblSale b ON a.id = b.customerid;
       ) c;
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 9, 2015 8:55 PM

All replies

  • User269602965 posted
    Use an INNER JOIN not a FULL OUTER JOIN with DISTINCT
    
    SELECT a.Name, a.JobType, b.Unit, b.Price
    FROM tblCustomer a
    INNER JOIN tblSale b ON a.id = b.customerid;
    
    2. 
    
    SELECT
      c.Name, 
      CASE 
        WHEN c.JobType = "FULL TIME" THEN ((c.Unit * c.Price) * 0.25) 
        WHEN c.JobType = "PART TIME" THEN ((c.Unit * c.Price) * 0.15)
       END AS Commission
    FROM 
      (SELECT a.Name, a.JobType, b.Unit, b.Price
       FROM tblCustomer a
       INNER JOIN tblSale b ON a.id = b.customerid;
       ) c;
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 9, 2015 8:55 PM
  • User269602965 posted

    FROM tblCustomer, tblSale

    This is rarely used.  What is returns is all possible combinations and permutations, the Cartesian product.

    Usually you will want to do INNER JOIN or LEFT OUTER JOIN to child tables.

    Thursday, December 10, 2015 6:32 PM