This is just a little something that I''ve been wanting to figure out for awhile. Just thought I''d shoot it out and see if anyone has done something like this: Say you have the following tables: products ------------ - id - name parts ------- - id - product_id - code - color_id colors -------- - id - color Performing the following query: SELECT * FROM products, parts, colors WHERE product_id = products.id AND color_id = colors.id Gives: 1 | Bicycle Tire | 1 | 1 | T01R | 1 | 1 | Red 1 | Bicycle Tire | 2 | 1 | T01G | 2 | 2 | Green 1 | Bicycle Tire | 3 | 1 | T01B | 3 | 3 | Blue Now, lets say you''d like to perform a query that outputed like this: 1 | Bicycle Tire | T01R | P01G | P01B Can this be done with SQL, and should it be done?
The SQL query will output what you select. You''re doing a select all, so it''s giving you everything. Try selectinig what you specifically want, in the order you want it (of course, you could also do an "order by" command in the query Carol Bean On 8/22/05, James Earl <jamesd.earl-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > This is just a little something that I''ve been wanting to figure out > for awhile. Just thought I''d shoot it out and see if anyone has done > something like this: > > Say you have the following tables: > > products > ------------ > - id > - name > > parts > ------- > - id > - product_id > - code > - color_id > > colors > -------- > - id > - color > > Performing the following query: > > SELECT * > FROM products, parts, colors > WHERE product_id = products.id <http://products.id> > AND color_id = colors.id <http://colors.id> > > Gives: > > 1 | Bicycle Tire | 1 | 1 | T01R | 1 | 1 | Red > 1 | Bicycle Tire | 2 | 1 | T01G | 2 | 2 | Green > 1 | Bicycle Tire | 3 | 1 | T01B | 3 | 3 | Blue > > Now, lets say you''d like to perform a query that outputed like this: > > 1 | Bicycle Tire | T01R | P01G | P01B > > Can this be done with SQL, and should it be done? > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 8/22/05, James Earl <jamesd.earl-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> This is just a little something that I''ve been wanting to figure out > for awhile. Just thought I''d shoot it out and see if anyone has done > something like this: > > Say you have the following tables: ><snip>> > 1 | Bicycle Tire | 1 | 1 | T01R | 1 | 1 | Red > 1 | Bicycle Tire | 2 | 1 | T01G | 2 | 2 | Green > 1 | Bicycle Tire | 3 | 1 | T01B | 3 | 3 | Blue > > Now, lets say you''d like to perform a query that outputed like this: > > 1 | Bicycle Tire | T01R | P01G | P01B > > Can this be done with SQL, and should it be done?I''ve run into this situation a few times. You can do what you''re asking, but it will be a headache. The usual advice is to let the data be and change its presentation in the display layer. http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4c1531b8a143b19e Searching google for the following returned some good results: sql table rows as columns.
On 8/22/05, Sam Kirchmeier <sam.kirchmeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''ve run into this situation a few times. You can do what you''re > asking, but it will be a headache. The usual advice is to let the data > be and change its presentation in the display layer. > > http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4c1531b8a143b19e > > Searching google for the following returned some good results: sql > table rows as columns.Thanks, I think I''ll take your advice and do this in the presentation layer!
On Mon, 2005-08-22 at 16:33 -0600, James Earl wrote:> This is just a little something that I''ve been wanting to figure out > for awhile. Just thought I''d shoot it out and see if anyone has done > something like this: > > Say you have the following tables: > > products > ------------ > - id > - name > > parts > ------- > - id > - product_id > - code > - color_id > > colors > -------- > - id > - color > > Performing the following query: > > SELECT * > FROM products, parts, colors > WHERE product_id = products.id > AND color_id = colors.id > > Gives: > > 1 | Bicycle Tire | 1 | 1 | T01R | 1 | 1 | Red > 1 | Bicycle Tire | 2 | 1 | T01G | 2 | 2 | Green > 1 | Bicycle Tire | 3 | 1 | T01B | 3 | 3 | Blue > > Now, lets say you''d like to perform a query that outputed like this: > > 1 | Bicycle Tire | T01R | P01G | P01B > > Can this be done with SQL, and should it be done?It could be done with some custom function. Here''s the problem though. When you build such a row, are you going to account for the possibility of having more parts for product X then product Y? For example: 1 | Bicycle Tire | T01R | P01G | P01B 2 | Hula Hoop | T01R | P01G | P0GB| XYZ1 | MP3S | RXZ8 @products.each do |product| puts @product.name @product.parts.each { |part| puts part.code.color... } end I''m sure that you could do something along those lines with AR, once you get the has_many, has_one, belongs_to mappings in place. Robby -- /****************************************************** * Robby Russell, Owner.Developer.Geek * PLANET ARGON, Open Source Solutions & Web Hosting * Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 * www.planetargon.com | www.robbyonrails.com *******************************************************/
On 8/22/05, Robby Russell <robby-/Lcn8Y7Ot69QmPsQ1CNsNQ@public.gmane.org> wrote:> It could be done with some custom function. Here''s the problem though. > > When you build such a row, are you going to account for the possibility > of having more parts for product X then product Y? > > For example: > > 1 | Bicycle Tire | T01R | P01G | P01B > > 2 | Hula Hoop | T01R | P01G | P0GB| XYZ1 | MP3S | RXZ8 > > > @products.each do |product| > puts @product.name > @product.parts.each { |part| puts part.code.color... } > end > > I''m sure that you could do something along those lines with AR, once you > get the has_many, has_one, belongs_to mappings in place. >This is a good point. I was thinking that the number of parts columns would come from the number of colors. So in this case, there would only be three columns: Red, Green, and Blue. There''s other issues with this though, as it limits how much you can describe a particular part (limit in my case is defined by the amount of info that can fit on an 8.5 x 11 piece of paper). For example, things would get really ugly if I needed to show the Size of each part. Instead of three columns you''d have six! Hmmm... not to mention if each part had a different price! I think I''m going to stick with the KISS principle on this one! :) Thanks