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.
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;
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:
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.
Contributor
3462 Points
1341 Posts
Re: PL/SQL queries to return different records
Dec 09, 2015 08:55 PM|Lannie|LINK
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.