SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    still battling category table

    Ok.. I'm still trying to figure out how to query a category table. I think my structure is right, but the query part is where I get confused....

    Table shoes
    shoe_id | shoe_name | shoe_cat | shoe_price | etc...

    Table categories
    category_id | category_name | parent_id

    Now there are several entries in the shoes table. They people are allowed to either enter in any detail they want for the shoes. Ie.. They can enter in just a top level, or a sub level or a sub-sub level...

    ie. Categories Table
    1 | Dress | null
    2 | casual | null
    3 | pump | 1
    4 | strapless | 3

    shoe table
    1 | crazy shoes | 4 | $45.99
    2 | fun shoes | 1 | $53.99

    So let's say I want to join these tables and find all Dress Shoes. crazy shoes is cat category 4, which has a parent_id of pump (category 3), which has a parent_id of category 1.

    I was told that this a left join issue???? I have no clue. Is this where I need to learn subselects?

  2. #2
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is hard to do without recursing queries if you want to do it to a potentially infinite level (you can't do infinite joins so you'd need to make a function which called itself to get the parent category until it hit parent_id = null). This is not too hard to do but is not very good performance-wise because of all the queries.
    There is another (more complicated!) way to do it but it means you can fetch all the parent or child rows in one go... read this sitepoint article for how to do it and the pros and cons of each approach: http://www.sitepoint.com/article/hie...-data-database

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by webgodjj
    I was told that this a left join issue????
    yes, it could be

    unless you have an infinite number of subcategories (heh), is there some upper limit to the number? like, maybe 3 or 4? i can't picture shoes being subcategorized any deeper than that

    if you know there is a maximum number of "subs" (as in sub-sub-sub-sub-sub-sub-...-categories), then you can retrieve everything in exactly one query without changing to the nested set model as that article seems to suggest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is still battling my head. Ok.. I figured out that there will be a maximum number of 3 levels to this. However, people can put in just two.
    They need to at least state if the item is a Men's/Womens, and they need to say if it is a shirt/pant/ect... However, there is also one more category after that they may optionally ad. The final id would be put into the user table.

    Ie...

    Mens -> Shirts -> T-Shirts
    Womens ->Dresses -> Summer Dresses.

    If I set the parent Id to 0 for Men's and womens, I can easily search for this and find all the clothing for that. However, one level deeper is where I can't figure out the code. So let's say that I need to find all shirts:

    TABLE CLOTHING_CATEGORIES
    id | name | parent
    1 mens 0
    2 womens 0
    3 shirts 1
    4 T-Shirts 2
    ...

    I would have another table that I think I would need to left join on? It would have the user's information...
    TABLE USER
    user_id | name | clothing_category ...

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why would you put a user into a clothing category? shouldn't you be putting actual items of clothing into the clothing categories?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no... the id from the clothing category table goes into the user table.....

  7. #7
    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)
    Quote Originally Posted by webgodjj
    no... the id from the clothing category table goes into the user table.....
    huh? since when are users pumps or strapless?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck
    huh? since when are users pumps or strapless?
    ROFL ROFL LMAO
    rudy.ca | @rudydotca
    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
  •