Left joins on the same table

Question

I'm using JPA Query Criteria to build a query with a join on the same table. The table looks like below.

Table A

Id  EventType        

A    Start                        
A    Start
A    End
B    Start
C    Start
C    End

I want to find out all rows whose EventType is Start and does not have EventType End for the same Id. In SQL: I got this with joins like this below:

   select DISTINCT *
     from (select *
             from A
            where Id='A'
              AND EventType = 'Start') startEventTable 
LEFT JOIN (select *
             from A
            where Id='A'
              AND EventType = 'endEvent') endEventTable 
       ON (startEventTable.Id = endEventTable.Id) 
    WHERE endEventTable.Id IS NULL;

I could not realize this in JPA

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(A.class);
Root<A> r1 = cq.from(A.class);
SubQuery sq1 = cq.subquery(A.class);
SubQuery sq2 = cq.subquery(A.class);
Root<A> r2 = sql.from(A.class);
Root<A> r3 = sq2.from(A.class);
sq1.where(<predicate>);
sq2.where(<predicate>);

cq.where(cb.and(cb,exists(sq1), cb.exists(sq2).not()));
New TypedQuery(cq).getResultList();

This fails with multiple path exceptions. I'm new to JPA queries, request someone to help here.

You don't need to do this with subqueries, which might be giving you the problem. What the heck is ``? You also have at least one other syntax error in there. — Clockwork-Muse, Aug 01 '14 at 09:49
There is no such thing as a left inner join. A join is *either* a left join *or* an inner join. — Bohemian, Aug 01 '14 at 09:56
is cb.equal(); There could be some syntax errors alright but I want to understand how to achieve this.. If subqueries is not the way to go, how to realize this — Chakravarthy Varaga, Aug 01 '14 at 09:59

score 1 · Accepted Answer · edited May 23 '17 at 11:49

Use one of these simpler and faster queries:

SELECT DISTINCT *  -- you probably don't need DISTINCT
FROM   A
WHERE  Id = 'A'
AND    EventType = 'Start'
AND    NOT EXISTS (
    SELECT 1
    FROM   A a1
    WHERE  a1.Id = A.Id
    AND    EventType = 'endEvent'
    );

Or

SELECT DISTINCT *  -- you probably don't need DISTINCT
FROM   A
LEFT   JOIN A a1 ON a1.Id = A.Id
                AND a1.EventType = 'endEvent'
WHERE  A.Id = 'A'
AND    A.EventType = 'Start'
AND    a1.Id IS NULL;

Details:

Select rows which are not present in other table

score 0 · Answer 2 · answered Aug 01 '14 at 11:50

0

You can express equivalent query in JPA:

SELECT *
FROM A
WHERE Id='A' AND EventType = 'Start'
  AND Id NOT IN (
        SELECT Id from A
        WHERE Id='A'
          AND EventType = 'endEvent')

using this approach.

answered Aug 01 '14 at 11:50

Roman-Stop RU aggression in UA

14,905
3
48
53

Left joins on the same table

2 Answers2