Existential quantifier - Mailing list pgsql-sql
From | Dag-Erling Smørgrav |
---|---|
Subject | Existential quantifier |
Date | |
Msg-id | 86bpkgqiv8.fsf@ds4.des.no Whole thread Raw |
Responses |
Re: Existential quantifier
Re: Existential quantifier |
List | pgsql-sql |
Consider the attached schema (filmstars.sql), which is a poorly designed database of films and actors. The following query gives me a list of films in which either Charlie or Martin Sheen starred: select fs.film.title, fs.film.year from fs.film left join fs.star on fs.film.id = fs.star.film where fs.star.last = 'Sheen' group by fs.film.title, fs.film.year; Is there a way to do this without the "group by" clause? DES -- Dag-Erling Smørgrav - des@des.no drop schema fs cascade; create schema fs; create table fs.film ( id serial not null primary key, title varchar not null unique, year integer not null ); create table fs.star ( id serial not null primary key, film integer not null references fs.film(id), last varchar not null, first varchar not null, unique (film, last, first) ); insert into fs.film(title, year) values ('Apocalypse Now', 1979); insert into fs.star(film, last, first) select id, 'Sheen', 'Martin' from fs.film where title = 'Apocalypse Now'; insert into fs.star(film, last, first) select id, 'Brando', 'Marlon' from fs.film where title = 'Apocalypse Now'; insert into fs.star(film, last, first) select id, 'Duvall', 'Robert' from fs.film where title = 'Apocalypse Now'; insert into fs.star(film, last, first) select id, 'Ford', 'Harrison' from fs.film where title = 'Apocalypse Now'; insert into fs.film(title, year) values ('Blade Runner', 1982); insert into fs.star(film, last, first) select id, 'Ford', 'Harrison' from fs.film where title = 'Blade Runner'; insert into fs.star(film, last, first) select id, 'Young', 'Sean' from fs.film where title = 'Blade Runner'; insert into fs.star(film, last, first) select id, 'Hauer', 'Rutger' from fs.film where title = 'Blade Runner'; insert into fs.star(film, last, first) select id, 'Hannah', 'Daryl' from fs.film where title = 'Blade Runner'; insert into fs.film(title, year) values ('Platoon', 1986); insert into fs.star(film, last, first) select id, 'Sheen', 'Charlie' from fs.film where title = 'Platoon'; insert into fs.star(film, last, first) select id, 'Dafoe', 'Willem' from fs.film where title = 'Platoon'; insert into fs.star(film, last, first) select id, 'Berenger', 'Tom' from fs.film where title = 'Platoon'; insert into fs.film(title, year) values ('Wall Street', 1987); insert into fs.star(film, last, first) select id, 'Douglas', 'Michael' from fs.film where title = 'Wall Street'; insert into fs.star(film, last, first) select id, 'Sheen', 'Charlie' from fs.film where title = 'Wall Street'; insert into fs.star(film, last, first) select id, 'Hannah', 'Daryl' from fs.film where title = 'Wall Street'; insert into fs.star(film, last, first) select id, 'Sheen', 'Martin' from fs.film where title = 'Wall Street';