JPA inner join query syntax help required

Question

I was wondering if you guys could help me with an OpenJPA query I am trying to write.
I have an ItemEntity...

@Entity(name = "item")
public class ItemEntity implements java.io.Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.TABLE, generator = "itemid")
    @TableGenerator(name = "itemid", table = "items_sequence", allocationSize = 1)
    private Long id;
    @ManyToOne
    private ImportPayloadEntity importPayloadEntity;

And a ImportPayloadEntity...

@Entity(name = "import_payload")
public class ImportPayloadEntity implements java.io.Serializable{
    @Id
    @GeneratedValue(strategy = GenerationType.TABLE, generator = "importpayloadid")
    @TableGenerator(name = "importpayloadid", table = "import_payload_sequence", allocationSize = 1)
    private Long id;
    @Column(name = "PROCESSED_IND")
    private String processedInd;

I am trying to select from my item entity joining back to my import payload entity to ensure that the processedInd is equal to 'N'.

I can't quite get the correct syntax. I've tried a few things but here is my latest attempt..

@NamedQuery(name = "queryItems", query = "SELECT c1, c2 FROM item c1 INNER JOIN c1.importPayloadEntity c2 WHERE c2.processedInd='N'")

This gives me...

Unknown column 't0.IMPORTPAYLOADENTITY_ID' in 'on clause' {prepstmnt 1719904819 SELECT t0.id, t1.id, t1.FILENAME, t1.LOAD_DATETIME, t1.IMPORT_PAYLOAD_BODY, t1.IMPORT_PAYLOAD_TYPE, t1.PROCESSED_DATETIME, t1.PROCESSED_IND FROM item t0 INNER JOIN import_payload t1 ON t0.IMPORTPAYLOADENTITY_ID = t1.id WHERE (t1.PROCESSED_IND = ?)} [code=1054, state=42S22]

Appreciate your help.

Answer 1

You can try select i from Item i where i.importPayloadEntity.processedInd = 'N'