Mysql SELECT nested query, very complicated?

Question

Okay, first following are my tables:

Table house:

id | items_id |

1 | 1,5,10,20 |

---

Table items:

id | room_name | refer

1 | kitchen | 3

5 | room1 | 10

---

Table kitchen:

id | detail_name | refer

3 | spoon | 4

5 | fork | 10

---

Table spoon:

id | name | color | price | quantity_available |

4 | spoon_a | white | 50 | 100 |

5 | spoon_b | black | 30 | 200 |

How to do a nested select statement, where I want to select id, name, color, price and quantity_available column, from the each value inside the 'items_id' column in 'house' table?

This is very challenging!!

EDIT:

after read robin's answer

Table house:

id | items_id |

house1 | 1 |

house1 | 5 |

house1 | 10 |

house2 | 20 |

If this it the house table, how to do the nested, join, or whatever select statement??

Answer 1

The complexity appear to be in your "house" table.

You should not store ID's as a comma separated list, instead, you should have a separate row for each of the item id's.

Once you split that out you should find that you can produce any results you need with just simple joins.