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:

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??

In the 'house' table, how is '1,5,10,20' stored? Just as a string with multiple numbers like that? — Alex Sexton
– Alex Sexton, Commented Oct 21, 2009 at 8:37
@Alex: yes Alex, currently is a string with comma as separator — smartbear
– smartbear, Commented Oct 21, 2009 at 8:43
Relationships between the tables are not very clear, at least to me. You should normalize the house table, but to help you with the select we need to know what ID references what. — Lukáš Lalinský
– Lukáš Lalinský, Commented Oct 21, 2009 at 8:47

Robin Day · Accepted Answer · 2009-10-21 08:36:18Z

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.

answered Oct 21, 2009 at 8:36

Robin Day

103k23 gold badges122 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

smartbear Over a year ago

@robin: if i separated the house string into rows, how to use the joins query?(simple joins??

Amber Over a year ago

Take a look at the W3Schools SQL Joins tutorial: w3schools.com/Sql/sql_join.asp

Collectives™ on Stack Overflow

Mysql SELECT nested query, very complicated?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related