On 9/7/07, Bill Moseley <moseley at hank.org> wrote: > > There is no reason to use distinct in your example; that is where the > > evilness comes from. Also, using inner views or actual views is a > > solution, but again I don't see any reason to do it that way. > > But, with the view I have I need some way to get "distinct" widgets. Yes, but just accomplish this without using the "distinct" keyword. The most common way to do this is using group by. > In simple terms, I want to do a join on all the tables (so I can say > WHERE color = 'red') but then throw out any duplicate widget ids -- > and I don't care which are thrown out. Then on this final table be > able to use LIMIT and ORDER BY. Ok, I think you pretty much had it, then. You had: SELECT p.id, p.widget_name, max(c.color) AS color, max(s.size) AS size FROM product p JOIN color c ON c.product = p.id JOIN size s ON s.product = p.id WHERE size = 'large' GROUP BY id, widget_name, color ORDER BY color; But you do not want to group by color. That is why we are using the aggregate function max() SELECT p.id, p.widget_name, max(c.color) AS color, max(s.size) AS size FROM product p JOIN color c ON c.product = p.id JOIN size s ON s.product = p.id WHERE size = 'large' GROUP BY id, widget_name ORDER BY max(color); Again, though, the resulting color and size combo may not be valid. Your other option is to forget about getting distinct results and take care of that when you render. Order by widget_name and have your application code suppress the row any time widget_name is the same as the previous row. This way you will get the equivalent of a first() aggregate function which ensures that the color-size combo is valid. Frankly, given that most products have only a single color-size combo, this is how I would approach the issue. -- Matt Warden Cincinnati, OH, USA http://mattwarden.com This email proudly and graciously contributes to entropy.