hql request with different tables-CodePudding

I want to write a complex hql query with relationships from 3 tables. It is necessary to make a selection of the selected columns so that they are located by ID

@Query("SELECT p.regId, p.method, p.tax, p.fee, p.netAmount, r.countSec, p.status "  
    "FROM P p INNER JOIN R r INNER JOIN D d on p.regId = r.id AND p.regId = d.id")
List<P> findAllByRegId(String regId);

My compiler cannot execute the request, I don't understand what the problem is! Help, please

org.postgresql.util.PSQLException: ERROR: syntax error at or near "join"

CodePudding user response：

The format for multiple joins is

SELECT columns ...
FROM table1 AS t1
   INNER JOIN
   table2 AS t2
   ON t1.id = t2.id
   INNER JOIN
   table3 AS t3
   ON t3.id = t1.id

CodePudding user response：

Your join is totally wrong. You have to add ON keyword and map primary key and foreign key in first inner join.

Here is your join, which is wrong

SELECT columns FROM A a
INNER JOIN B b
INNER JOIN C c ON a.ColName = b.ColName AND a.ColName = c.ColName

Here is the correct syntex for multiple join

SELECT columns FROM A a
INNER JOIN B b ON a.ColName = b.ColName
INNER JOIN C c ON a.ColName = c.ColName

Here down is modified query

@Query("SELECT p.regId, p.method, p.tax, p.fee, p.netAmount, r.countSec, p.status "  
    "FROM P p INNER JOIN R r ON p.regId = r.id INNER JOIN D d ON p.regId = d.id")