Need to have subquery within subquery

发布于 2020-03-27 10:17:24

I have a stock table which holds for example

 Partnumber | Depot | flag_redundant
------------+-------+----------------
          1 |     1 |              5
          1 |     2 |              0
          1 |     3 |              0              
          1 |     4 |              5
          2 |     1 |              0
          2 |     2 |              0
          2 |     3 |              0
          2 |     4 |              0

I need to be able to see the depots in which the parts have not been flagged as redundant, but the flag_redundant has been at least been flagged once for that part, and I need to ignore any parts where there has not been a flag flagged.

Any help appreciated!

I'm thinking of something along the lines of ....

SELECT stock.part, stock.depot, 
       OrderCount = (SELECT CASE WHEN Stock.flag_redundant = 5 THEN 1 end as Countcolumn FROM stock C)
  FROM stock 

 Partnumber | MissingDepots
------------+---------------
          1 | Yes

forpas 2019-07-03 22:28

You can group by partnumber and set the conditions in the HAVING clause:

select 
  partnumber, 'Yes' MissingDepots 
from stock 
group by partnumber
having 
  sum(flag_redundant) > 0 and 
  sum(case when flag_redundant = 0 then 1 end) > 0

Or:

select 
  partnumber, 'Yes' MissingDepots 
from stock 
group by partnumber
having sum(case when flag_redundant = 0 then 1 end) between 1 and count(*) - 1

See the demo.
Results:

> partnumber | missingdepots
> ---------: | :------------
>          1 | Yes

Related issues

Query is possibly slow?

Getting empty rows from "SELECT ALL" query in PostgreSQL

Implement smart search by text field in database

Is it posible to use queried column_name in INSERT INTO statement?

What is the different between replica and db instance in Aurora Postgresql?

Entity Framework Core Many to Many change navigation property names

Django

How can i use longitude and latitude with typeorm and postgres

Keeping annotated rank when filtering Django

Liquibase validation fails when UNIQUE constraint