CODEWITHSUNDEEP
sqlpostgresqlfunctionaggregateselection
sebajou
sebajou

Reputation: 3

Discriminate data from functions aggregate on PosgreSQL

On PosgreSQL I have a database of pizza restaurant. With this code:

SELECT command.id_command, array_agg(history_state_of_command.state)
FROM command JOIN history_state_of_command 
    ON command.id_command = history_state_of_command.id_command
GROUP BY command.id_command

I obtain these results, with the id of a command and the associated state of command:

command.id_command State of command

1 "{Pizza_Order,Pizza_in_preparation,Pizza_prepared,Pizza_ready_for_delivery,Pizza_delivering,Pizza_deliver}"

2 "{Pizza_Order,Pizza_in_preparation}"

3 "{Pizza_Order,Pizza_in_preparation,Pizza_prepared,Pizza_ready_for_delivery,Pizza_delivering,Pizza_deliver,"Command cancelled"}"

4 "{Pizza_Order,Pizza_in_preparation,Pizza_prepared,Pizza_ready_for_delivery,Pizza_delivering,Pizza_deliver}"

I would like to find an SQL code where I obtain only id of command where the pizza was never prepared: command.id_command State of command 2 "{Pizza_Order,Pizza_in_preparation}"

Many thanks for your help !

Upvotes: 0

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

You can use aggregation as well:

select hsc.id_command
from history_state_of_command hsc
group by hsc.id_command
having count(*) filter (where hsc.state = 'Pizza_prepared') = 0;

Note: This assumes that commands have some row in the history. If not, then use not exists;

select c.*
from command c
where not exists (select 1
                  from history_state_of_command hsc
                  where hsc.id_command = c.id_command and hsc.state = 'Pizza_prepared'
                 );

This is probably the most efficient method, with appropriate indexes.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48850

You can use a correlated subquery to find this command:

select h.id_command
from history_state_of_command h
where h.state in ('Pizza_Order', 'Pizza_in_preparation')
  and not exists (
    select 1 
    from history_state_of_command i
    where i.id_command = h.id_command and i.state = 'Pizza_prepared'
  )

Upvotes: 2

Related Questions