views:

41

answers:

2

I have a table with products.
When I get information from that table, I would also like to get the ETA of that article. To do so, I am planning to get the latest purchase Order Row, that is on this article, and then get the expected delivery of this purchase.

This is three different tables and I would like it to be like another column on the query, so I can get the value from the column like I would if it was on the same table.

Is my idea possible? If there is no purchase order on this article I would like the value to be null.

Products

Int ProductId
Int Price

Sample data

ProductId  Price
-----------------
1          100
2          300

PORows

Int RowId
Int ProductId
Int POId

Sample data

RowId  ProductId  POId
-----------------------
1      1          1

PO

Int POId
DateTime ETA

Sample data

POId  ETA
-----------------------
1     2010-10-25 10:05

So the result I would want is:

ProductId   Price  ETA (null if no rows exist)
------------------------------------------------
1           100    2010-10-25 10:05
2           300    NULL
A: 

I don't know Pervasive but in SQL standard you can make the select for the latest PO an aliased subquery

select Products.id, products.name, ProductETAS.ETA
from Products
left join

( 
  select POLINES.productid, min(PO.ETA) as ETA from PO inner join POLINES
   on PO.id = POLINES.POid and POLINES.productid = ?
   where PO.ETA >= today 
   group by POLINES.productid
 ) as ProductETAS

 on Products.productid = ProductETAS.productid
Tim
A: 

Use:

   SELECT p.productid,
          p.price,
          x.max_eta
     FROM PRODUCTS p
LEFT JOIN POROWS r ON r.productid = p.productid
LEFT JOIN (SELECT po.id,
                  MAX(po.eta) AS max_eta
             FROM PO po 
         GROUP BY po.id) x ON x.poid = r.poid

Pervasive is the only database I'm aware of that won't allow you to omit the INNER and OUTER keywords. v10 might've relaxed that, but I know it's the case for v8 and 2000.

OMG Ponies
@OMG Ponies, Missed one of the key components, now any product that does not have an associated POROW will be left out. Btw the INNER and OUTER keywords can be left out now
Oskar Kjellin
@Oskar Kjellin: See update, glad to see v10 allows omitting words that every other DB allows too.
OMG Ponies
@OMG Ponies: Thank you very much!
Oskar Kjellin
@OMG Ponies: I thought of something, what if we have multiple PO? We need to get only the latest ETA available. Where should I put the order by?
Oskar Kjellin
@Oskar Kjellin: See update, but I don't have Pervasive handy to test with.
OMG Ponies
@OMG Ponies: Nooow it worked as epected :) Thanks! :)
Oskar Kjellin