• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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?      
0
czechmate1976
Asked:
czechmate1976
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

0
 
SharathData EngineerCommented:
If you are still getting one record, then try this.

Select SALEITEM.prodcode, product.producttitle, saleitem.quantity,
       case when productprice.startdate <= customersale.saledate then productprice.retailprice else 0 end 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 customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010')

(or)

Select SALEITEM.prodcode, product.producttitle, saleitem.quantity,
       decode(productprice.startdate <= customersale.saledate,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 customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010')
0
 
shru_0409Commented:
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');

u should be add left outer join as mention above expert....
b├žoz in productprice table does not have the data of second product data..

0
 
HainKurtSr. System AnalystCommented:
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');
0
 
awking00Commented:
Can you post the retailprice, startdate, and enddate from productprice where prodcode = 31035?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now