MySQL Help with parent ids

Posted on 2012-08-28
Last Modified: 2012-08-28
I have a table of data which gives characteristics of a product.

size: small, big, massive etc.
color: brown, blue, green etc.

Each product is either a parent or a child

If a child doesn't have a color specified in the record then it inherits the color from its parent.

The same for sizes.

Each parent has colors and sizes specified.

How can I construct an sql statement so that I can get all products (including parents) which are blue and also large. Obviously the sql statement must take into account that some products will have to inherit whilst others will have.

(I am using PHP and mySQL)

Thank you
Question by:shafferb1
    LVL 107

    Expert Comment

    by:Ray Paseur
    Please post the CREATE TABLE statements for these, thanks.

    Author Comment

    Sorry. I don't have a create table statement and have actually simplified as the join is running across about 11 tables.

    However, some sample data would be:

    id      productname                  color  size      isparent      parentid
    1      blue widget                  blue        large      yes                  
    2      green widget                   green  medium      yes
    3      small blue widget                    small      no                  1
    4      round blue widget                              no                  1
    LVL 142

    Accepted Solution

    this kind of queries works only simply in mysql, lacking the hierarchy queries techniques from MSSQL 2005+ or oracle 8+, when the data is stored in a special way:

    otherwise, you would need to create a function that for each product "id", check the relevant property in the hierarchy, but which risks to be quite inefficient. ...

    Author Comment

    So, for the 2nd way that you describe, I would have to query all products and then run through them. If they don't have a value for size or for color then I would then have to query their parent to find those values.

    is that the general technique?

    Thank you.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    yes, exactly
    LVL 107

    Expert Comment

    by:Ray Paseur
    I was kind of afraid that would be what we saw.  My suggestion would be to change the design so that each product has one complete row in the products table.  Then you can go up a level in the hierarchy and create the table of groups, categories, etc.  My point is that in retail sales and inventory, each SKU represents a unique item, and the more complete your data is in its description of the item, the less likely your programming will make a mistake as it manipulates that data.

    To get the CREATE TABLE statements, you can just run a query.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to count occurrences of each item in an array.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now