The prodcat contains all product id along with their category id The product table contains each product description The store_product contains a storeid, productid and price. This table is used because the product can exist in multiple stores and the prices can vary for each store.
I have got a session bean (facade) which has got a lookup for the prodcat. I have created a relationship also for all the tables.
Problem 1:- ----------- Given a categoryid, display details about all the products.
I wrote a select method in prodcat with the following EJB-QL :- " select object(p) from Prodcat c, IN(c.product) p where c.categoryid=?1 "
THIS WORKS FINE. I get a local home for product and then uses it's getter methods to get the details.
Problem 2:- ----------- Given a categoryid and storeid, display details about all the products along with the prices, belonging to that particular store..
This is where I am stuck now . Will I be able to write this in one EJB-QL. Because I can only return a localhome for product only... hwich deosn't contain the price from the other table.
Or do I need to first get the products and then loop thru it and do a lookup in store_products ???
I tried "select object(p) from prodcat c IN(c.prodcat) p , IN(p.storeproduct) s where p.id=s.productid and c.catid=?1 and s.id=?2"
Your help will be much appreciated.
regds Giju [ October 18, 2004: Message edited by: Giju George ]