On Sun, 5 Mar 2000, Brian Hirt wrote:
> Actually, the query you supply will not work, I'll get duplicate
> rows because the relationship between game and game_developer is
> a one to many.
Got me. I tried to read into it a little like 'one developer develops many
games' but apparently it's the other way around. In that case you could
use DISTINCT or maybe DISTINCT ON depending on the details.
> I dont know too much about the PG internals, but when I used sybase,
> it would usually execute the sub-select independently and stuff the
> results into a temp table and then do another query, joining to the
> results of the sub-select.
Last time I checked PostgreSQL executes the subquery for each row.
Apparently it must still be doing that and I do suspect that it is right
in the overall sense because the subquery may have side effects. Consider
SELECT * FROM t1 WHERE id IN (select nextval('my_sequence'))
Of course this query makes absolutely no sense whatsoever but perhaps
there are similar ones where it does.
But I didn't mean to bash your query style, just pointing out a
work-around that's commonly suggested. (People have been caught by this
before.)
> > SELECT creation_timestamp, etc. ^^ insert DISTINCT
> > FROM game, game_developer
> > WHERE game.game_id = game_developer.game_id
> > AND approved = 1 AND developer_id = 3
> > ORDER BY copyright_year desc, game_title
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
