SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    join, subquery or what?

    Hi,

    Here is a simplified version of the problem I'm trying to solve:

    I have two tables - items and options

    Code:
    items:
    item_name: varchar
    options: varchar holds comma-separated list of option_ids from options table
    
    options:
    option_id :unique int
    option_name:varchar
    
    Let's say I have 3 rows in the options table:
    
    option_id   option_name
    3             Red
    4             Large
    5             Plastic
    
    and 3 the rows in the items table:
    
    item_name     options
    Bucket           3,5
    Jacket            3,4,5
    Hat               3,4
    
    Is there a single query that I can do that will return this:
    
    Item             Options
    Bucket          Red,Plastic
    Jacke            Red,Large,Plastic
    Hat               Red,Large
    thanks for any help you can offer

  2. #2
    SitePoint Zealot CdeMky's Avatar
    Join Date
    Sep 2004
    Location
    United States
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It looks like you need a third table to "map" between options and items since this appears to be a many-to-many relation.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    nope. because the options in the items table are stored in one field, you can't do that in SQL

    cdemnky is exactly right: you need a third table to link the items to options.

  4. #4
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and if they were in a separate table? I still don't think there is a single query that would get me that data...I guess there is one more hitch. There is a qty field associated with the item and what I want is:

    3 Bucket Red,Large
    9 Jacket Red,Large,Plastic
    7 Bucket Red,Small
    4 Hat Red, Large

    where the number is the total number of items with that combination of options. (using the comma-separated list, that part is simple...I just select distinct combinations and group by that column). I don't really see how having a third table makes this particular problem any easier.

    Thanks

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by datahost
    I don't really see how having a third table makes this particular problem any easier.
    try it

    you will find that various queries (such as, oh, for example, translating all the option_ids to their option_names) become trivially easy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Has anyone actually looked at the question I asked? I know how to use table joins to get data from multiple tables.

    I am asking if there is a query that will produce a very specific result. I don't think the particular result I want will be any easier- in fact I think it would be harder if not impossible if I use a third table.

    Here is the query I used that almost got the result I am after (using the actual database and not the simplified example I gave:

    select concat(catalog_id,'-',product_name,':',option1,':',option2,':',option3,fancy_option) as Product, (select concat(qty_sku_no,' ',qty_option_text) from qty_prices where qty_price_id in (order_items.fancy_option)) as Options, sum(qty) as Quantity,sum(price_sold) as Sales from order_items left join products on products.product_id = order_items.product_id left join orders on orders.order_id = order_items.order_id where orders.order_status = 'New' && order_items.price_sold != 0 group by Product order by Product

    the problem is that only one value is returned from the qty_prices table. I don't think a third table will actually produce the result I'm after. If someone can show me how, I'll look at that as a possibility.

    Presently I have an alternate solution that involves using several queries to produce the summary data I'm after.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, i have actually looked at the question you asked

    yes, i am familiar with the issue, and no, you're not going to be able to solve it

    the problem is, you cannot do this part --

    ... where qty_price_id in (order_items.fancy_option)

    it just doesn't work, despite looking so promising

    on the other hand, having a separate table for the many-to-many items-to-options relationship is very feasible

    you may think that it will not produce the result you want, but i can assure you it will

    i tried working your query into this new configuration but i got lost when it came to the qty_prices table and its qty_sku_no column
    Code:
    select catalog_id
         , product_name
         , count??? sum??? qty  as Options
         , sum(qty) as Quantity
         , sum(price_sold) as Sales 
      from order_items 
    inner
      join products 
        on order_items.product_id 
         = products.product_id 
    inner
      join product_options 
        on products.product_id      
         = product_options.product_id
    inner
      join options 
        on product_options.option_id     
         = options.option_id
    inner
      join orders 
        on order_items.order_id
         = orders.order_id  
     where orders.order_status = 'New' 
       and order_items.price_sold != 0 
    group 
        by catalog_id
         , product_name 
    order 
        by catalog_id
         , product_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your thoughtful answer... I think the direction you're going with that will produce:

    item option
    hat Red
    hat Large
    hat Plastic
    bucket Red
    bucket Large

    the question I'm trying to answer is how many

    large red hats
    small red hats
    large red buckets
    red plastic buckets
    large plastic buckets

    where large, red, plastic are stored in a separate table with various other attributes (price, price type, etc) and, of course, not limiting myself to a fixed number options.

    using a the third table to join the options and items, how can you get a result that shows you all the distinct combinations of options for a given product. If you can figure it out in this simple form, I can figure out how to work it into the more detailed query I gave.

    using the comma-delimited list (in an 'options' field) this does work to get the number of distinct combos:

    "select distinct item.options from items"

    what I've done, and I think it will work, is get the distinct combos with the above query. then I loop through those and build an indexed array with the option_ids and the option_text, like:

    $array_option['4,5'] => 'Large,Red';
    $array_option['5,7'] => 'Red,Plastic';

    Then I loop through the $array_option array and select the totals I'm after from the items table. It works, but I was wondering if a subquery could return a value from more than one row, I guess not?

    anyway, I don't absolutely NEED to do this in a single query, I was just wondering if it could be done.

    thanks again

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the part i'm very uncomfortable with is the looping and arrays

    if you enjoy that sort of coding, and please don't take this the wrong way, then knock yourself out

    i am confident, from experience, that you can get what you want in one query if the tables are designed correctly

    but the question of identifying a "sku hierarchy" is by no means a trivial issue, as one might wish to classify products by multiple dimensions -- size, colour, mens'/women's, category, ...

    i'm not going to design your tables for you, sorry -- it's saturday

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well...I can wait until Monday...



    I certainly agree that the looping is not very elegant.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •