CODEWITHSUNDEEP
sqlspring-data-jpa
Saphyra
Saphyra

Reputation: 460

How to query? (sql / jpql)

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.

Upvotes: 1

Views: 33

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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
               );

Upvotes: 1

Related Questions