On Sep 21, 2010, at 6:30 PM, Tom Lane wrote: > Ogden <lists@xxxxxxxxxxxxxx> writes: >> SELECT tr.id, tr.sid >> FROM >> test_registration tr, >> INNER JOIN test_registration_result r on (tr.id = r.test_registration_id) >> WHERE. >> tr.test_administration_id='32a22b12-aa21-11df-a606-96551e8f4e4c'::uuid >> GROUP BY tr.id, tr.sid > > Seeing that tr.id is a primary key, I think you might be a lot better > off if you avoided the inner join and group by. I think what you really > want here is something like > > SELECT tr.id, tr.sid > FROM > test_registration tr > WHERE > tr.test_administration_id='32a22b12-aa21-11df-a606-96551e8f4e4c'::uuid > AND EXISTS(SELECT 1 FROM test_registration_result r > WHERE tr.id = r.test_registration_id) > > regards, tom lane > Thank you for this suggestion, however, what if I wanted some columns from test_registration_result - this wouldn't work, for example if I wanted test_registration_result.answer to be fetched. Hence, I had to have a JOIN with test_registration_result and a GROUP BY. I still am not happy with my query - the EXISTS executes in great speed however I cannot retrieve any of the columns from that table. Thank you Ogden -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
