Android sqlite/Ms sql / sql query

Question

I have Product Table & priceDeatil table.

 Prouct Table
 ProductCode  BusinessUnit 
 10001        ORB          
 10002        ORB          

 StockRecord Table
 ProductCode    Name       StcokQuantity 
  10001         SUnSilk    1000
  10002         Pen         500

priceDeatil 
ProductCode  BusinessUnit  price   DateFrom     DateTo
10001         ORB          12.00   12-08-2011   31-09-2015
10001         ORB          21.00   01.08-2011   15-09-2011
10002         ORB          54.00   21.08-2011   15-09-2011

I want to get the product table's record,StockRecords's table & price with that product latest price

Here productCode 10001 contain 2 records when I run query it return 2 reords.I want to get only one records.

 SELECT     WMProduct.BusinessUnit, WMProduct.ProductCode, StockRecord.Name, WMPriceDetail.Price
 FROM       WMProduct INNER JOIN StockRecord
            ON WMProduct.ProductCode = StockRecord.ProductCode
           INNER JOIN WMPriceDetail
            WMPriceDetail ON WMProduct.BusinessUnit = WMPriceDetail.BusinessUnit AND WMPriceDetail.ProductCode = WMProduct.ProductCode

 WHERE     (WMPriceDetail.DateFrom < GETDATE()) AND (WMPriceDetail.DateTo > GETDATE() OR
            WMPriceDetail.DateTo = NULL)

This query returns

   BusinessUnit ProductCode     Name   Price
     ORB         10001         SunSilk    12.00
     ORB         10001         SunSilk    21.00

But I need only one records.... BusinessUnit ProductCode Name Price ORB 10001 SunSilk 21.00 ORB 10002 Pen 54.00

From that query i can't put TOP 1 because there are more table combine.. join

If the price is not define in the price detail table, that records should not show into the list.

Please help me....

Thanks in advance

cetver · Accepted Answer · 2011-09-02 09:00:24Z

1

how i uderstood you need only max price from priceDetail:

SELECT WMProduct.BusinessUnit,
       WMProduct.ProductCode ,
       WMProduct.Description ,
       price_detail.price
FROM   WMProduct
       INNER JOIN
              ( SELECT  MAX(Price) AS price,
                       BusinessUnit        ,
                       ProductCode
              FROM     WMPriceDetail
              GROUP BY BusinessUnit,
                       ProductCode
              )
              price_detail
       ON     price_detail.BusinessUnit = WMProduct.BusinessUnit
       AND    price_detail.ProductCode  = WMProduct.ProductCode
WHERE  (
              WMPriceDetail.DateFrom < GETDATE()
       )
AND
       (
              WMPriceDetail.DateTo > GETDATE()
       OR     WMPriceDetail.DateTo = NULL
       )

answered Sep 2, 2011 at 9:00

cetver

11.9k6 gold badges42 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Piraba Over a year ago

It say The multi-part identifier "WMPriceDetail.DateFrom" could not be bound.. And anotherthing not 'MAX(Price)' . It should be 'MAX(DateFrom)'

cetver Over a year ago

a forgot about where =) add in subselect fields which you need and not forget about groupping

Piraba Over a year ago

I think you assumption is wrong.I need latest define price(DateFrom) and that date rage should not be finish date(compare with GETDATE()).Not working.I got error msg

Blootac · Accepted Answer · 2011-09-01 09:04:00Z

0

You can use a top 1 with a join...

Just do select top 1 etc. as you've already done. so

select top 1 a.name, b.name from table1 a join table2 b on a.id=b.id

Your second problem is that you're not necessarily going to get the correct row returned as you aren't ordering the results before doing the top 1.

--Update

Seems sqlite doesn't understand top but does understand LIMIT. See http://www.sqlite.org/lang_select.html

Note that you will still need to use an order by clause

edited Sep 1, 2011 at 9:04

answered Aug 31, 2011 at 9:47

Blootac

5431 gold badge4 silver badges19 bronze badges

Comments

Piraba · Accepted Answer · 2011-09-06 10:16:33Z

0

+50

Try this friend:

 select p.businessunit, p.productcode, pr.price from
 WMProduct p ,WMStockRecord s, WMPriceDetail pr
 where p.productcode = s.productcode and
    s.productcode = pr.productcode and p.productcode=pr.productcode and
    pr.datefrom = (select max(datefrom) from WMPriceDetail where productcode = p.productcode  and
  (
    ( DateFrom < 'Sep 06 2011') AND (DateTo > 'Sep 06 2011' OR DateTo IS NULL) 
  )

edited Sep 6, 2011 at 10:16

answered Sep 6, 2011 at 10:03

Piraba

7,00618 gold badges87 silver badges140 bronze badges

Collectives™ on Stack Overflow

Android sqlite/Ms sql / sql query

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related