SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    needing to establish a set of cases for available inventory

    Here is an area I simply have no experience with. I have to evaluate for my available inventory on a per product basis. I have to evaluate for all avaialbe sizes: e.g. S, M, L, XL, 2X.

    I can establish a basic query for this search but I am only able to search on one characteristic at a time.
    Code MySQL:
    SELECT i.inventory_available 
       FROM exp_foxee_skus_options_inventory AS i
    INNER 
        JOIN exp_foxee_skus_options AS o
          ON i.opt_val_id = o.value_id
         AND o.field_id = "1" // get just mens
    WHERE sku = '26' // value dynamic equal to my {entry_id}
         AND value = "S"
    I would assume this is a good opportunity to implement a set of cases but I simply do not know how to.

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the WHERE clause contains two unqualified column names, so it's a little hard to understand

    which table do they belong to?

    also, please note, you should ~not~ use quotes areound numeric values if they are being compared to numeric columns (and especially not mix the types of quotes)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    also, if you are joining to the options table, but aren't returning any data from it, then what is the purpose of the join? you will simply return each i.inventory_available value once for every option
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello, thanks for replying.

    I'm sorry for the lack of clarity on my end.

    Code MySQL:
    SELECT i.inventory_available
       FROM exp_foxee_skus_options_inventory AS i
    INNER
        JOIN exp_foxee_skus_options AS o
          ON i.opt_val_id = o.value_id
         AND o.field_id = 1
    WHERE i.sku = 26
    I do infact need both tables involved. "exp_foxee_skus_options_inventory" contains my product counts where "exp_foxee_skus_options" provides each of my 5 classifications.

    The corrected query above returns all of my values for each of the classifications but I need a vehicle for evaluating each specifically; e.g. S, M, L, XL, 2X.

    I hope this makes better sense.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    have you tried
    Code:
    SELECT i.inventory_available
         , o.value
      FROM ...
    ORDER
        BY o.value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, in fact I have.

    And yes all the data is returned however I'm not sure I can use in this fashion.

    I was thinking it might be possible to return the data in a manner that allows me to refer to "inventory_available" for as a single variable for each of my classifications which would then allow me to evaluate for "Small" and then "Medium", and so on...

    Otherwise I'm looking at needing to run a query 5 individual times for each of my sizes.

    Code MySQL:
    SELECT i.inventory_available
       FROM exp_foxee_skus_options_inventory AS i
    INNER
        JOIN exp_foxee_skus_options AS o
          ON i.opt_val_id = o.value_id
         AND o.field_id = 1
         AND o.value = 'Small'
    WHERE i.sku = 26
    I was thinking that this can't be the most efficient way to get these as indavidual values.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hothousegraphix View Post
    I was thinking it might be possible to return the data in a manner that allows me to refer to "inventory_available" for as a single variable for each of my classifications which would then allow me to evaluate for "Small" and then "Medium", and so on...
    this is a task for the programming language -- all the data is there, you don't need to run 5 queries to get 5 sets of results

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

  8. #8
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it. Thanks


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
  •