Here is my two entities :

```
@Table(name = "Table1")
@Entity
public class Table1 {
private Long id;
private Table2 table2;
private String field2;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

@ManyToOne(optional = false)
@JoinColumn(name = "field1")
public Table2 getTable2() {
    return table2;
}

public void setTable2(Table2 table2) {
    this.table2 = table2;
}

@Column(name = "field2")
public String getField2() {
    return field2;
}

public void setField2(String field2) {
    this.field2 = field2;
}
}

@Table(name = "table2")
@Entity
public class Table2 {
private Long id;
private List<Table1> table1List;
private String field5;
private boolean field4;
private String field3;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID", insertable = false, updatable = false)
public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

@OneToMany(mappedBy = "table2", cascade = CascadeType.ALL)
@LazyCollection(LazyCollectionOption.FALSE)
public List<Table1> getTable1List() {
    return table1lList;
}

public void setTable1List(List<Table1> table1List) {
    this.table1List = table1List;
}

@Column(name = "field5")
public String getField5() {
    return field5;
}

public void setField5(String field5) {
    this.field5 = field5;
}

@Column(name = "field4")
public boolean getField4() {
    return field4;
}

public void setField4(boolean field4) {
    this.field4 = field4;
}

@Column(name = "field3")
public String getField3() {
    return field3;
}

public void setField3(String field3) {
    this.field3 = field3;
}

```

}

I write my SQL query to return a duplicate rows from table1, then filter the result using other criteria.

```
SELECT p1.*
 FROM table1 p1, table2 d
  JOIN (
     SELECT field1, field2, COUNT(*)
       FROM table1
        GROUP BY field1, field2
         HAVING count(*) > 1 
     ) p2
  ON 
  p1.field1 = p2.field1 AND
  p1.field2 = p2.field2 and 
  d.id = p1.field1 and
  d.field3 IN ('A', 'B') and 
  d.field4 = 0 and
  d.field5 not in ('DD', 'MM', 'FF', 'RR')
ORDER BY p1.field2

```

I tried to translate my query to HQL :

```
SELECT p1 FROM Table1 p1, Table2 d 
  WHERE (p1.table2.id, p1.field1) in (
         SELECT table2.id, field2 FROM (
            SELECT pp.table2.id, pp.field2, COUNT(pp)
                FROM table1 pp
                GROUP BY pp.table2.id, pp.field2
                HAVING count(pp) > 1 
         )
        ) AND
        p.table2.id = d.id AND
        d.field3 IN (:list3) AND
        d.field4 = false AND
        d.field5 NOT IN (:list5)

```

When running my app, I get this errors :

```
Sep 23, 2020 6:45:17 PM org.hibernate.hql.ast.ErrorCounter reportError
SEVERE: line 1:263: unexpected token: (
<Sep 23, 2020, 6:45:17,203 PM CEST> <Error> <org.hibernate.hql.PARSER> <BEA-000000> <line 1:263: unexpected token: (> 
Sep 23, 2020 6:45:17 PM org.hibernate.hql.ast.ErrorCounter reportError
SEVERE: line 1:308: unexpected token: COUNT
<Sep 23, 2020, 6:45:17,208 PM CEST> <Error> <org.hibernate.hql.PARSER> <BEA-000000> <line 1:308: unexpected token: COUNT> 
Sep 23, 2020 6:45:17 PM org.hibernate.hql.ast.ErrorCounter reportError
SEVERE: line 1:318: unexpected token: FROM
<Sep 23, 2020, 6:45:17,209 PM CEST> <Error> <org.hibernate.hql.PARSER> <BEA-000000> <line 1:318: unexpected token: FROM>.

```

Any help will be appreciated.
Thank you in Advance.