help regarding EJB-QL

[Giju George]

Hi guys,
I need help in writing an EJB-QL. I have 3 tables...


Prodcat
-------
categoryid
productid

Product
-------
id
barcode
description

Store_product
-------------
id
storeid
productid
price


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.

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 ]

[Karthik Guru]

Not sure if this makes sense,

select object(sp) from Store_Product sp , IN (sp.product.categories) category where sp.storeid = ?1 and category.id = ?2

I need to get to Store_Product since i want the price for a product for a given store.

- this will return a collection of Store_products.

Once I have those rows selected , I can get to the product by running through the collection and accessing the corresponding product CMR?

i mean sp.product should get me to product details