Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

Create view in postgresql

Hallo i'm new to postgresql and i can't achieve this result:

table 1 "pizza":
id_pizza
name_pizza

table 2 "pizza ingredient":
id_pizza
ingredient_name
ingredient_available

example data:

table1:
1 margherita
2 wurstel
3 cheese

table2
1 tomato           true
1 mozzarella    true
2 wurstel          true
3 cheese1        true
3 chesse2        true
3 tomato           false

i would like create a view that return this result, if possible:

pizzas view:
1 margherita   available
2 wurstel         available
3 cheese         not  available


thanks to all

0
wakatanka
Asked:
wakatanka
  • 2
  • 2
1 Solution
 
k_murli_krishnaCommented:
I need a small clarification to be able to answer your question. On what basis available and not available are being decided under pizzas view. Why cheese is coming as not available?.

As far as I can understand, in table2, once one false is coming against tomato then tomato should be shown as not available. Is this correct?
0
 
earth man2Commented:
something like

create view available_pizza as
  select a.pizza_name, case when
(select min(b.ingredient_available) from pizza_ingredient  b where b.id_pizza = a.id_pizza)  then
'pizza available'
else
'pizza unavailable' as pizza_availability from pizza a;
0
 
wakatankaAuthor Commented:
@earyman2

thanks for your entry, but b.ingredienti_available is a boolean and the min function cannot be used.

@k_murli_krishna:
Pizza are not available, when just 1 of his ingredient is not available, ie: if tomato is false, margherita and cheese pizza are not available. (i correct the initial schema posted).
0
 
earth man2Commented:
drop view available_pizza;
drop table pizza;
create table pizza ( id_pizza int, pizza_name varchar(40));
drop table pizza_ingredient;
create table pizza_ingredient( id_pizza int, ingredient_name varchar(40), ingredient_available boolean );

insert into pizza values (1, 'margherita'),(2 ,'wurstel'),(3,' cheese');

insert into pizza_ingredient values (1, 'tomato',   true),
(1, 'mozzarella',    true),
(2, 'wurstel',          true ),
 (3, 'cheese1'  ,      true ),
 (3, 'chesse2',        true ),
 (3, 'tomato' ,          false );
 
create view available_pizza as
  select a.pizza_name, case when
(select bool_and(b.ingredient_available) from pizza_ingredient  b where b.id_pizza = a.id_pizza)  then
'pizza available'
else
'pizza unavailable' end pizza_availability from pizza a;

select * from available_pizza;
0
 
wakatankaAuthor Commented:
it works flawless, thank you so much, you are the man.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now