> > It's strange and I've never quite put my finger on why, but I never use > DISTINCT on > > my own databases. I occasionally seem to need to use it on inherited > databases, > > though, and I see it in online tutorials all the time. > >Well, SELECT DISTINCT says that there are multiple rows of data that >have identical values, a good clue that the data is likely not >normalized <s>.
There are many situations where a query returns multiple identical rows because the query parameters include multiple possible matching criteria. Thinking off the top of my head: Show me an unduplicated list of names of people who have at least one of several matching characteristics and features: SELECT firstname, lastname, pk FROM bunchapeople ; JOIN characteristics on fk = bunchapeople.pk AND color == "Blue" AND size = 8 ; UNION ; SELECT firstname, lastname, pk FROM bunchapeople ; JOIN characteristics on fk = bunchapeople.pk AND color == "Red" AND size = 7 ; UNION ; SELECT firstname, lastname, pk FROM bunchapeople ; JOIN features on fk = bunchapeople, pk ... etc....etc.... Just a dumb example; not trying to be precise. But it's the general case where if you are looking to get a single parent record whose child records may, but do not have to, match more than one parameter, you're going to get multiple duplicate records in the results even though you only need one. These are "OR" situations. You can spend hours trying to write a 300 word query that correctly handles all the AND/OR conditions to filter out all but one record....or you can use DISTINCT. I don't think that's because the data is denormalized, I think it's because SQL is more or less "clunky". Ken Dibble www.stic-cil.org _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
