Create view, showing all unreserved seats
I have two tables: In one, I save all seats for a specific room. In the other table, data for the reservations of the seats is stored.
Now, i would like to create a view, where I have a column which marks a seat as marked or unmarked.
Or more abstract: I would like to give out true/false in a table column for a view, if a certain number(the seatnumber) appears in the reservationtable.
Thanks in advance
PS: I use postgres
Table definitions开发者_如何转开发:
View "public.seats"
Column | Type | Modifiers
------------+---------+-----------
ticketname | text |
price | integer |
colour | text |
snr | integer | <- thats thes seatnumber, primary key
px | integer |
py | integer |
rnr | integer | <- the roomnumber, not relevant
Table "public.reservation"
Column | Type | Modifiers
--------+---------+-----------
spnr | integer |
snr | integer | <- references seats
knr | integer |
CREATE OR REPLACE VIEW seat_reserved
AS
SELECT s.snr seat,
CASE COALESCE(r.snr,-1) WHEN -1 THEN false ELSE true END reserved
FROM seats s
LEFT OUTER JOIN reservation r on r.snr = s.snr
select s.*, exists ( select * from reservation r where r.snr = s.snr ) as reserved from seats
Select st.*, decode(res.snr,null,'false','true') reserved
from seats st,
reservation res
where res.snr (+) = st.snr
精彩评论