How to query? (sql / jpql)

Question

I need to create a query, but a it difficult one (at least or me).

I have Products stored in the db, each one belongs to a Factory.
Each Factory can work only one of its own Product at the same time.
Each Product have a number represents its order in the Factory's queue.
Each product has a field what is a number if its Factory has started working on it, or null if the Product is in the queue.

The query must return a list of Products, with the first Product of each Factory's queue, but only if the Factory is not working on any product currently.

See the following example:

Given:

Id          FactoryId   Working     Order
Product1    Factory1    4654        1

Product2    Factory2    9841        3
Product3    Factory2    NULL        6

Product4    Factory3    NULL        4
Product5    Factory3    NULL        7

Product6    Factory4    NULL        2
Product7    Factory4    3211        9

product8    Factory5    NULL        10
product9    Factory5    NULL        5
product10   Factory5    NULL        13

Result:

Id          FactoryId   Working     Order
Product4    Factory3    NULL        4
product9    Factory5    NULL        5

I use Spring Data JpaRepository.

Yogesh Sharma · Accepted Answer · 2018-06-28 14:43:14Z

1

You can use NOT EXISTS with correlated subquery :

select t.*
from table t
where not exists (select 1 
                  from table t1 
                  where t1.FactoryId = t.FactoryId and 
                        t1.working is not null) and
       Order = (select min(t1.order) 
                from table t1 
                where t1.FactoryId = t.FactoryId
               );

answered Jun 28, 2018 at 14:43

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

1 Comment

Saphyra Over a year ago

I needed some time to translate it to jpql, but it works. Thanks!

Collectives™ on Stack Overflow

How to query? (sql / jpql)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related