troubleshooting Question

Can you do an "intra-table join"?

Avatar of SiobhanElara
SiobhanElaraFlag for United States of America asked on
MySQL Server
4 Comments2 Solutions291 ViewsLast Modified:
I've inherited a table that contains a list of products. For the sake of simplicity, we'll say the columns are as follows:

ID (the auto-incremented value)
name
description
size
subsetOf
price

The field subsetOf can either be a number that is 0, meaning it's a "base" product, or some other integer, referring to another ID. If it's not 0, it contains a different size and price from the base product with a blank name and description (since those fields should be the same as the base.)

If this were in two separate tables, you could obviously do a join on subsetOf = ID and not have the name or description fields in the second table at all. As it stands, if I do a query for a couple of IDs I may wind up with the following:

ID   name          description             size   subsetOf   price
3    green shirt   a nifty green shirt     L      0          5.00
4                                          XL     3          9.00

Is there a way within the query to get instead:
ID   name          description             size   subsetOf   price
3    green shirt   a nifty green shirt     L      0          5.00
4    green shirt   a nifty green shirt     XL     3          9.00

Thanks!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros