Hi there. I have a little problem with a sql-query I'm trying to get together. Well I got it to work halfways. I have a table with items, which are composed of 1-6 other items, which is saved in the same table. You might call it a recursive query, I need to get the names of the components that makes up the item I search for. It looks about like this: (3 CompX removed for readability) ID Name Comp1 Comp2 Comp3 1 Item1 0 0 0 2 Item2 0 0 0 3 Item3 0 0 0 4 Item4 0 0 0 5 Item5 1 3 4
Item5 is a composition of item 1,3 and 4. It would be no problem if I only wanted to get the ID of the Included components but I want the names. I tried using: "SELECT i.Name, c1.Name, c2.Name, c3.Name FROM Items AS i, Items AS c1, Items AS c2, Items AS c3 WHERE i.ID=5 AND i.Comp1=c1.ID AND i.Comp2=c2.ID AND i.Comp3=c3.ID;" This gives me the right result, but if an Item only consists of 2 components then I don't get anything Which is understandable. And I can only imagine what kinds of resources it will take if the Items table Gets really large. Is there a better way to do this? I want to keep the number of queries to a minimum. I have read some on Union, joins and subqueries but I can't think of a way to make it work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
