I need help in writing an EJB-QL. I have 3 tables...
Prodcat * --------------- 1 Product 1 ------------------- * Store_product
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.
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.
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 ???
"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.
[ October 18, 2004: Message edited by: Giju George ]