CODEWITHSUNDEEP
jpamany-to-many
uriel
uriel

Reputation: 1248

How to get records from table based on two other tables JPA

I'm trying to do something simple in JPA.

I have a table Businesses:

 BusinessId    name
------------  ------ 
     1         Joe
     2         bob

And table Products:

 productID     name
------------  ------ 
     1         Pen
     2         paper

Because they related as meny-to-many I created another table businessesHasProductID:

 BusinessId    productID
------------  ----------- 
      1            1
      1            2
      2            2

Now I want to select BusinessId and productID form businessesHasProductID where the name of BusinessId = 'x' and the name of productID = 'y'.

I built the tables and then I created the entity classes (from wizard in netBeans). I know how to get the "Businesses" table where Businesses.name = 'x' and I know how to get "Products" table where Products.name = 'y'. but I want to combine these results and get the IDs.

I tried to do :

Query query = getEntityManager().createQuery("
              SELECT b FROM businessesHasProductID WHERE b.BusinessId IN 
                  (SELECT t0.BusinessId FROM Businesses t0 WHERE t0.BusinessId = 'x')
                AND b.productID IN
                  (SELECT t1.productID FROM Products t1 WHERE t1.productID = 'y')
              ");

That's not worked. It complains that the IN contains invalid data.

Upvotes: 0

Views: 331

Answers (1)

JB Nizet
JB Nizet

Reputation: 691903

If I understand correctly, you want to get all the [bId, pId] tuples that exist in the join table and for which the name of the business identified by bId is 'x' and the name of the product identified by pId is 'y'.

If so, the following query should do what you want:

select business.businessId, product.productId 
from Business business
inner join business.products product
where business.name = 'x'
and product.name = 'y'

Upvotes: 2

Related Questions