shafferb1
asked on
MySQL Help with parent ids
I have a table of data which gives characteristics of a product.
eg.
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
eg.
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
Ray Paseur
Please post the CREATE TABLE statements for these, thanks.
ASKER
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
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
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
is that the general technique?
Thank you.
yes, exactly
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.
http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html
To get the CREATE TABLE statements, you can just run a query.
http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html