[SQL] Existential quantifier
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 - d...@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'; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Existential quantifier
On Sat, 10 Oct 2009, [utf-8] Dag-Erling Sm??rgrav wrote: 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? Not at all tested as I don't have access to my db right now, but I think something like one of these would work: select fs.film.title, fs.film.year from fs.film where exists(select 1 from fs.star where fs.film.id = fs.star.film and fs.star.last = 'Sheen'); select fs.film.title, fs.film.year from fs.film where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen'); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Existential quantifier
you could use distinct on select distinct on (fs.film.title, fs.film.year ) title, year from fs.film left join fs.star on fs.film.id = fs.star.film where fs.star.last = 'Sheen'; On Sat, 2009-10-10 at 00:53 +0200, Dag-Erling Smørgrav wrote: 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 plain text document attachment (filmstars.sql) 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'; -- Rick Albright Senior Quantitative Analyst Insiderscore LLC ralbri...@insiderscore.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Existential quantifier
Richard Albright ralbri...@insiderscore.com writes: you could use distinct on select distinct on (fs.film.title, fs.film.year ) title, year from fs.film left join fs.star on fs.film.id = fs.star.film where fs.star.last = 'Sheen'; Thanks, I didn't know about distinct on. This version uses the primary (surrogage) key: select distinct on (fs.film.id) 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'; DES -- Dag-Erling Smørgrav - d...@des.no -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Existential quantifier
Stephan Szabo ssz...@megazone.bigpanda.com writes: Not at all tested as I don't have access to my db right now, but I think something like one of these would work: select fs.film.title, fs.film.year from fs.film where exists(select 1 from fs.star where fs.film.id = fs.star.film and fs.star.last = 'Sheen'); Ah, that was exactly what I was looking for. select fs.film.title, fs.film.year from fs.film where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen'); ITYM select fs.film.title, fs.film.year from fs.film where fs.film.id in ( select fs.star.film from fs.star where fs.star.last = 'Sheen' ); (missing FROM) DES -- Dag-Erling Smørgrav - d...@des.no -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Existential quantifier
On Sat, 10 Oct 2009, [utf-8] Dag-Erling Sm??rgrav wrote: Stephan Szabo ssz...@megazone.bigpanda.com writes: Not at all tested as I don't have access to my db right now, but I think something like one of these would work: select fs.film.title, fs.film.year from fs.film where exists(select 1 from fs.star where fs.film.id = fs.star.film and fs.star.last = 'Sheen'); Ah, that was exactly what I was looking for. select fs.film.title, fs.film.year from fs.film where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen'); ITYM select fs.film.title, fs.film.year from fs.film where fs.film.id in ( select fs.star.film from fs.star where fs.star.last = 'Sheen' ); (missing FROM) Yeah, that'd be necessary. You might want to try them on a realistic data set to see how the various options are planned. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql