Click here to Skip to main content
14,551,234 members

Comments by carloscs (Top 1 by date)

carloscs 20-Apr-20 15:01pm View
   
With this schema you can't do it without using distinct or group by. [If you had a third PlaceLoc table with unique key columns (PlaceId, Loc) having all the place_locs you could do it].

However a slightly clear and more effective query is this one:

select p.PlaceId, p.Loc, d.DayId
from Place p, Day d
where not exists (select 1 from Place p2 where p2.PlaceId = p.PlaceId and p2.Loc = p.Loc and p2.DayId = d.DayId)
group by p.PlaceId, p.Loc, d.DayId

- it clearly tells what you wand to do - the other query is a bit obfuscated.
- the not exists correlated subquery should be a bit more optimized that the left join [allthough this can vary with differnte databases depending on the optimizer]

- note that the group by has the same effect as using distinct on the select part [I'm old school and prefer group by's :)]

Edit: query not tested, just a quick example