mysql - How to do multiple joins and access table from outer query from subquery -

select * items join item_types on it.itemnumber = ( select itemnumber item_types it2 join item_index_info iii on it2.itemnumber=iii.parent , it2.`type` ='electronics' , it2.`value`='televisions' , iii.child=i.itemnumber order iii.`index` limit 1); 

so tables have following structure:

1.items has columns:

 itemnumber, availability, quantity, parent 

2.item_types has columns:

id, itemnumber, type, value 

3.item_index_info has columns:

parent, child, index (in table parent can child item well)

using above query fetch items of type easy find tricky part item_type might not associated each , every child lets child might not have entry in item_index_column parent might (that why have second query have limit 1 beacuse unfortunately child might have parents @ multiple levels i.e. indexes have find first parent (lowest index) has entry in item_types table)

please let me know if missing information.

p.s. in above query error saying i.itemnumber unknown column guess because trying access table outer query.

items

itemnumber  availibility    quantity    parent item001         y             10         item99 item002         y             10         item65 item003         y             10         item012 item004         y             10         item67 item005         y             10         item006 item006         y             10         item43 item007         y             10         item87 item008         y             10         item57 item009         y             10         item89 item010         y             10         item33 item011         y             10         item68 item012         y             10         item34 item99          y             10         item560 item560         y             10         item1234 

item_types

id  itemnumber  type         value 1     item011     electronics   xyz1 2     item56                xyz2 3     item012        b        xyz3 4     item67         c        xyz4 5     item006        d        xyz5 6     item87         e        xyz6 7     item57         f        xyz7 8     item89         g        xyz8 9     item33         h        xyz9 10    item68                xyz010 11    item34         j        xyz011 12    item560      toys       xyz012 13    item1234     gels       xyz013  item_index_info  parent       child    index item99      item001    0 item560     item001    1 item1234    item001    2 item560     item99     0 item1234    item560    0 item002     item65     0 item003     item012    0 item003     item34     1 item012     item34     0 item004     item67     0 item006     item005    0 

some points note: 1. parents in first table children in same table: e.g. item012 parent in row no: 3 child in row no: 12 (but not necessarily)

2. all children or parents might not have entry in item_types e.g. item99 has no entry in second table (its parent , child too)

3. now table 3 maintains how deep relationships e.g. @ record no.1 item001 direct child of item99 index 0 indirect child of item560 index 1 , indirect child of item1234 item560 child of item1234 third record has index 2 , on children , parents.. item560 parent item has parent have entry in table

ok query items fall under 'toys' going on each , every item item001 doesn't have entry in second table parent's category selected. first parent encounter in third table have stop there (lowest index)

because can see last record in second table has item1234 has category 'gels'. item1234 parent of item560 still have different item types item99 have stop @ lowest index i.e. item560 immediate parent found , move on.

so here sequence, item001 didnt find entry in table 2 choose first parent i.e. item99 did not have entry in table 2 moved on third parent , found record in table 2 stop there

another example: item005 no entry in table 2 move parent item006 @ index 0 find entry in table 2 stop here

if understand properly, this. have not tried it

select in.itemnumber, in.quantity join item_types on it.itemnumber=in.itemnumber it.type = "toys" union select in.itemnumber, in.quantity join item_index_info io on io.child=in.itemnumber join item_types on it.itemnumber=io.oparent it.type = "toys" 

i suggest getting each part of query working , use union