title: How to do multiple joins and access table from outer query from subquery
body: <pre><code>select * from items as i join item_types it on it.itemnumber = (
  select itemnumber from item_types as it2 join item_index_info as iii on it2.itemnumber=iii.parent and it2.`type` ='ELECTRONICS' AND it2.`value`='TELEVISIONS'
  AND iii.child=i.itemnumber order by iii.`index` limit 1);
  </code></pre>
  
  <p>so the tables have following structure:</p>
  
  <p>1.items has columns: </p>
  
  <pre><code> itemnumber, availability, quantity, parent
  </code></pre>
  
  <p>2.item_types has columns: </p>
  
  <pre><code>id, itemnumber, type, value
  </code></pre>
  
  <p>3.item_index_info has columns: </p>
  
  <p><code>parent, child, index</code> (In this table the parent can be a child to another item as well)</p>
  
  <p>Using the above query I would like to fetch all items that are of a certain type which is easy to find but tricky part is that item_type might not be associated to each and 
  every child so sometimes lets say child might not have any entry in item_index_column but its parent might (that is why we have the second query but then we have to limit it to 1
  beacuse unfortunately the child might have parents at multiple levels i.e. indexes so we have to find the first parent (lowest index) who has an entry in item_types table)</p>
  
  <p>Please let me know if I am missing any information. </p>
  
  <p>P.S. in the above query I get an error saying i.itemnumber Unknown Column I guess because I am trying to access table from outer query. </p>
  
  <p>items</p>
  
  <pre><code>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
  </code></pre>
  
  <p>item_types</p>
  
  <pre><code>id  itemnumber  type         value
  1     item011     Electronics   xyz1
  2     item56         A        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         I        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
  </code></pre>
  
  <p>Some points to note:
  1. Parents in the first table are also children in the same table:
  for e.g. item012 is parent in row no: 3 but child in row no: 12 (but not necessarily)</p>
  
  <ol start="2">
  <li><p>all children or parents might not have an entry in item_types
  for e.g. item99 has no entry in the second table (its a parent and a child too)</p></li>
  <li><p>Now table three maintains how deep the relationships are for e.g. look at record no.1 item001 is direct child of item99 so index 0
  But it is indirect child of item560 so index 1 and also indirect child of item1234 as item560 is child of item1234 so third record has index 2
  and so on for all the children and parents.. Even the item560 that is a parent to an item has a parent so it will also have an entry in the table</p></li>
  </ol>
  
  <p>Ok now I would like to query all the items that fall under 'Toys' by going over each and every item but item001 doesn't have an entry in the
  second table so now its parent's category will be selected. But the first parent we encounter in the third table we have to stop there (lowest index)</p>
  
  <p>Because as you can see the last record in the second table has item1234 which has category 'Gels'. item1234 is parent of item560 but still have
  different item types so for item99 we have to stop at lowest index i.e. item560 immediate parent that we found and move on.</p>
  
  <p>So here is the sequence, for item001 we didnt find an entry in table 2 so we choose the first parent i.e. item99 which also did not have an entry in
  the table 2 so we moved on third parent and found the record in table 2 so we stop there</p>
  
  <p>another example: for item005 no entry in table 2 so we move to parent item006 at index 0 we find the entry in table 2 so we stop here</p>