PL/SQL queries to return different records[Answered] RSS

• • Reply
  avt2k7

  Member

  169 Points

  414 Posts

  PL/SQL queries to return different records

  Dec 09, 2015 05:55 PM|avt2k7|LINK

  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.  

• • Reply
  Lannie

  Contributor

  3462 Points

  1341 Posts

  Re: PL/SQL queries to return different records

  Dec 09, 2015 08:55 PM|Lannie|LINK

  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;
  

• • Reply
  Lannie

  Contributor

  3462 Points

  1341 Posts

  Re: PL/SQL queries to return different records

  Dec 10, 2015 06:32 PM|Lannie|LINK

  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.