SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    complicated (join ?) query, could use some advice

    Hello, I have some trouble finding the right way to select something.

    Let's say I have a table with columns "productcode, boxlocation, productposition" . This is for a warehouse where a lot of boxes are stored (each with its own unique location number) and every box has several hundred products in it, all placed behind eachother. The productcode is a number to verify what type/version of product it is, its not a unique id. This column has an index.

    When all the data was inserted, there was a check to make sure every combination of productcode/boxlocation was unique. So even if a box has 10 pieces of a specific product, only one row was inserted for it. The position column indicates in what order each product is stored in its box. So the first in the box has position 1 , the second (assuming its different type of product then nr 1) got position 2 in its row, etc



    The simple searches that I need to perform are like "select boxlocation from warehouse where productcode='X' " , so that we can find the right box and take that product X out of it. Those kind of things work fine.

    But sometimes there are more complex requests about a group of products. What kind of query would I need to find "all boxes that contain a product X and a product Y right behind it" ? I stored the productposition just for this purpose, but I dont know how to use it correctly :s

    Is this possible to do, without changing the table structure too much ?

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select b1.boxlocation
    from box as b1 inner join box as b2
    on b1.boxlocation = b2.boxlocation
    where b1.productcode = 'X'
    and b2.productcode = 'Y'
    and b1.productposition + 1 = b2.productposition

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    swampBoogie, thanks a lot

    Haven't tested it yet, but I understand how it should work

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It works correctly, but it's pretty slow sometimes :s

    Any advice on how to use indeces for this ? Should I use a combined index for the "on" and "where" parts, or give each their own index ? I dont mind the extra hd space, as long as it searches fast enough to be useable on a site. Several of the tests gave me times of 50 to 2 seconds, whereas I consider 0.5 seconds acceptable for a query.

    I extended the query a bit too, added a "distinct"/"group by" to make the boxlocation results really uniqe, a "order by" on a new column for the price and a LIMIT. And probably made it even more complicated then it already was

    Code:
    select  DISTINCT (b1.boxlocation)
    from box as b1 inner join box as b2
    on b1.boxlocation = b2.boxlocation
    where b1.productcode = 'X'
    and b2.productcode = 'Y'
    and b1.productposition + 1 = b2.productposition
    GROUP BY b1.boxlocation
    ORDER BY b1.price DESC				
    LIMIT $p,20

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by odoisc
    ... added a "distinct"/"group by" to make the boxlocation results really unique
    LOL

    is that like "a little bit pregnant"?

    groups are, by definition, unique

    if you use GROUP BY, adding DISTINCT doesn't make the groups "really unique"


    ... a "order by" on a new column for the price
    some databases will not let you ORDER BY a column that isn't selected

    especially when there's grouping involved

    after the grouping, you have a bunch of box locations, and you can't sort on the price, because by then the groupings don't have a price column any more
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •