How to create nested selects with sql?

Question

I want to select all entities that are tied by a custom hierarchy:

@Entity
class Employee {
    @Id
    private long id;

    @ManyToOne
    private Company company;
}

@Entity
class Company {
    @Id
    private long id;

    @ManyToOne
    private LeisureGroup leisureGroup;
}

@Entity
class LeisureGroup {
    @Id
    private long id;
}

//select all companies that belong to a LeisureGroup

Select * from company where leisureGroupId = '123'

TODO: how can I select all employees that belong to the LeisureGroup (tied by the Company reference)? Do I have to use joins here? If yes, how?

You don't use SQL if you want to query through JPA, you use JPQL (Java Persistence Query Language) or HQL (Hibernate Query Language) — Tobb
– Tobb, Commented Jun 8, 2015 at 14:32

Safwan Hijazi · Accepted Answer · 2015-06-08 17:18:39Z

2

try something like this:

Select e from Employee e where e.company.id= (select c.id from Company c where c. leisureGroup.id=:id

edited Jun 8, 2015 at 17:18

answered Jun 8, 2015 at 14:39

Safwan Hijazi

2,0792 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

membersound Over a year ago

I want to select all employees`, not companies!

membersound Over a year ago

This works if there is only one company assigned to a leisuregroup. But for 2+ companies assigned this will return "result has more than one row". But this will mostly be the case due to the @ManyToOne.

Tobb Over a year ago

Is this a JPQL query or SQL query?

Tobb · Accepted Answer · 2015-06-08 15:12:00Z

1

You don't use SQL if you want to query through JPA, you use JPQL (Java Persistence Query Language) or HQL (Hibernate Query Language) (for Hibernate users).

JPQL query (requires an EntityManager variable called em)

public List<Employee> findEmployeesInLeisureGroup(final Integer leisureGroupId) {
    final TypedQuery<Employee> query =
        em.createQuery("select e " +
                       "from Employee e join e.company c join c.leisureGroup l " +
                       "where l.id = :leisureGroupId", Employee.class);
    query.setParameter("leisureGroupId", leisureGroupId);
    return query.getResultList();
}

SQL equivalent:

select * from Employee
where company_id in (select id from Company where leisureGroup_id = 123);

edited Jun 8, 2015 at 15:12

answered Jun 8, 2015 at 14:38

Tobb

12.3k6 gold badges60 silver badges83 bronze badges

3 Comments

Tobb Over a year ago

I'm programming directly in StackOverflow here, no guarantee it will actually work :p I can't really put my finger on why it doesn't work, but at least you have an example of joining with JPQL. Also, don't expect to run this as SQL, this is a JPA query.

membersound Over a year ago

Your sql will only work if the subselect returns only one row/one result. But there will mostly be more than one company assigned to the same leisuregroup...

Tobb Over a year ago

Fixed, found that I didn't need to reference the LeisureGroup table but forgot to change the = to in..

Collectives™ on Stack Overflow

How to create nested selects with sql?

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related