0

I have two tables:

Part: id, partnumber Alternative: partid, alternativepartid

Let's say that Part contains the following records:

  • 1 - part 1 2
  • 2 - part 2
  • 3 - part 3
  • 4 - part 4
  • 5 - part 5

And Alternative contains the following records:

  • 1 - 2
  • 1 - 3
  • 2 - 4
  • 3 - 5

I would like to create a query to get all parts that are related to a given id.

In my case that would mean that if I query for part 1 (id 1) I would like to find:

2 and 3 but also 4 and 5 as 4 is an alternative to 2 and 5 is an alternative to 3.

When I query for 3, I would like to find: 5, 1, 2, and 4

Does this make sense?

Would it be possible to retrieve the complete set of records in one query?

Thanks!

Mark
  • 45
  • 1
  • 6
  • I think [this](http://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159) is similar to what you want. – Bernhard Barker Dec 22 '12 at 13:01
  • This would have been possible with Common Table Expressions in SQLServer, but MySQL doesn't support them. So, you may have to use a stored procedure for this. – Vikdor Dec 22 '12 at 13:01
  • Just a suggestion but you could try to do this with a set of SQL Rules that follow these product chains before they return results. – L0j1k Dec 22 '12 at 13:02
  • 1
    Question: How many levels deep does this chaining go? – L0j1k Dec 22 '12 at 13:07
  • It could be infinite I guess as one alternative may point to any other alternative. – Mark Dec 23 '12 at 11:18

0 Answers0