asked on

SQL query

This query:
Select saleitem.prodcode, product.producttitle, saleitem.quantity
FROM customersale, saleitem, product
WHERE customersale.salref = 730175
AND customersale.salref = saleitem.salref
AND saleitem.prodcode = product.prodcode

generates this result:
PRODCODE PRODUCTTITLE QUANTITY
---------------------- -------------------- ----------------------
31035 arrow set of 3 1
31030 wooden table 2

This is what I am after.. but if I add the table PRODUCTPRICE in the FROM section like this:
Select SALEITEM.prodcode, product.producttitle, saleitem.quantity, productprice.retailprice
FROM customersale, saleitem, product, productprice
WHERE customersale.salref = 730175
AND customersale.salref = saleitem.salref
AND saleitem.prodcode = product.prodcode
AND product.prodcode = productprice.prodcode
AND productprice.startdate <= customersale.saledate
AND customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010');

it leaves me only with this:

PRODCODE PRODUCTTITLE QUANTITY RETAILPRICE
---------------------- -------------------- ---------------------- ----------------------
31030 wooden table 2 8.05

Can someone help please?

Raja Jegan R

This should bring both the records..

Select SALEITEM.prodcode, product.producttitle, saleitem.quantity, isnull(productprice.retailprice, 0) retailprice
FROM customersale
inner join saleitem on customersale.salref = saleitem.salref
inner join product on saleitem.prodcode = product.prodcode
left outer join productprice on product.prodcode = productprice.prodcode
WHERE customersale.salref = 730175
AND productprice.startdate <= customersale.saledate
AND customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010')

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

shru_0409

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

say your original query is Q (added saledate)

Select saleitem.prodcode, product.producttitle, saleitem.quantity
FROM customersale, saleitem, product, saledate
WHERE customersale.salref = 730175
AND customersale.salref = saleitem.salref
AND saleitem.prodcode = product.prodcode

then use this

select q.*, p.RETAILPRICE
from (Q) q left join productprice p on q.prodcode=p.prodcode
and p.startdate <= q.saledate
and q.saledate <= nvl(p.enddate, '03-JAN-2010');

awking00

Can you post the retailprice, startdate, and enddate from productprice where prodcode = 31035?