[thelist] Problem with distinct

[r937]

> Distinct is evil

it most certainly can be perplexing in the wrong hands

but it isn't evil, like ENUM, or SELECT *

the worst DISTINCT offenders? php programmers who think it's a function --

   select distinct(widget), color, size, ...

;o)

bill, there is no getting around the fact that if you are going to mush up
your data (e.g. print "red" when in fact it comes in red and blue), you are
going to have to live with unexpected and unintended consequences

a word to the wise is sufficient

if a widget comes in multiple sizes and colours, and you wish to show only
one row for each widget, my advice is to go ahead and show only one row per
widget, ~collapsing~ the multiplicity, ~not~ misrepresenting it (e.g. by
showing only one colour when it has two)

   widget A   red        large
   widget B   red/blue   medium
   widget C   green      small,medium
   widget D   blue       large
   widget E   blue/green medium/large
   widget F   blue       small
   widget G   red        large

if you're using MySQL, you are halfway home -- use the GROUP_CONCAT function

with any other database, good luck getting the same effect as the
GROUP_CONCAT function

if it's not obvious, i'm a big fan of the GROUP_CONCAT function, and feel it
should be voted the single most useful database function invented this
millenium (so far)

is this how you assemble your product_view?

  FROM product p
   JOIN color c ON c.product = p.id
   JOIN size s ON s.product = p.id

this would be where you apply the GROUP_CONCAT


rudy
http://r937.com/