mysql - in a sub query im trying to select from a table that was defined in outer query -

hi im getting "error code: 1146. table 'sakila.t' doesn't exist" on next sql code.

select distinct address.address   (          select inventory.store_id,                  count(inventory.inventory_id) num_of_items                 inventory                 group inventory.store_id        ) t,        address,        store,  t.num_of_items <= (select num_of_items t)    , store.store_id = t.store_id     , store.address_id = address.address_id 

from comment supposed return address of store has smallest number of items in inventory.

i think because im trying information t in sub query, , t variable , isnt defined in sakila database. problem? if yes why? if not, please tell me problem code. thanks.

to kind of sql query work, need break down task.

first, you're trying figure out how many items each store contains. easy, , have right, think.

     select inventory.store_id,              count(inventory.inventory_id) num_of_items        inventory       group inventory.store_id 

next, want figure out store has fewest number of items. this.

     select inventory.store_id,              count(inventory.inventory_id) num_of_items        inventory        group inventory.store_id        order count(inventory.inventory_id) asc        limit 1 

next, nest inside query finds data describing stores.

select address.address   (        select inventory.store_id,                count(inventory.inventory_id) num_of_items          inventory          group inventory.store_id          order count(inventory.inventory_id)          limit 1        ) t   join store on t.store_id = store.store_id   join address on store.address_id = address.address_id 

and that's result need. trick here use aggregate subquery (count() ... order ... limit 1) find particular item need, use bunch ofjoin` clauses gather data need display item found.

when debug , troubleshoot kind of thing, start simplest query, , build query set step.