MySQL Help with parent ids

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
Who is Participating?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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. ...
Ray PaseurCommented:
Please post the CREATE TABLE statements for these, thanks.
shafferb1Author Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

shafferb1Author Commented:
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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, exactly
Ray PaseurCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.