Search code examples
javasqlspringhibernatejpql

JPQL cannot join tables

Can you help me to join these tables people. My JPQL query is not working 

@Query("Select bp, p from Perks as p, BokPerks as bp where bp.bookingID =:bookingID and bp.payment_id = p.id")
    List<Payment> getTEST(@Param("bookingId")long bookingID);

Can't solve it for an hour already, maybe its a simple mistake but I already blocked a bit :) Table for BokPerks entity:

'131', NULL, '2'
'131', NULL, '3'
'132', NULL, '1'
'132', NULL, '2'
'132', NULL, '3'
'15', '1', '4'
'33', '2', '1'
'33', '2', '2'
'33', '2', '3'
'38', '3', '4'

perks table:

'1', 'lorem ipsum'
'2', '1234'
'3', 'hello '
'4', 'examples'
Solution

  • Your query doe not have JOIN. Should be something like this

    @Query("Select bp, p from Perks as p INNER JOIN p.BokPerks as bp where tbp.bookingID =:bookingID and tbp.payment_id = p.id")

    I wrote INNER JOIN p.BokPerks but actually there should be name of property referenced. Can't tell exact syntax without your entities classes

    Suppose BokPerks has perk reference property. Then the query should be 

    from  BokPerks as bp INNER JOIN bp.perk as p