Three tables: LISTING, BROKER, LISTING_BROKER
Each LISTING can have one or more BROKERs so LISTING_BROKER is a link.
Should not the following be equivalent?
SELECT distinct(l.listing_id)
FROM listing l, broker b, listing_broker lb
WHERE l.listing_id = lb.listing_id
AND b.broker_id = lb.broker_id
AND b.company_id = 1;
SELECT distinct on (l.listing_id) l.listing_id
FROM listing l, broker b, listing_broker lb
WHERE l.listing_id = lb.listing_id
AND b.broker_id = lb.broker_id
AND b.company_id = 1;
The first SELECT does NOT work in that the distinct() is ignored. The
second SELECT works correctly. Am I missing something?
