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.


Comments

Post a Comment

Popular posts from this blog

google app engine - 403 Forbidden POST - Flask WTForms -

i have flask application running on google app engine. trying submit form built using wtform , keep getting following error. 403 forbidden: don't have permission access requested resource. either read-protected or not readable server. location.html (part of code) <form method=post action="/home/location"> .... <button type="submit" class="btn btn-primary" value="submit"> directions </button> </form> main.py @app.route('/home/location', methods=['post', 'get']) def location(): form = cfcdirections.direction(request.form) print(request.method) if request.method == 'post' , form.validate(): print("after if") directions = cfcdirections() street_no = directions.no street = directions.street suburb = directions.suburb postcode = directions.postcode ...
Read more

Android layout hidden on keyboard show -

i have strange problem layout. when tap on edittext , keyboard shown edittext stay on bottom , when tap on to hide keyboard , keyboard hides , edittext go should when want input text. when tap again, keyboard shown , edittext goes down behind keyboard... <relativelayout android:layout_width="match_parent" android:layout_height="match_parent" > <scrollview android:layout_width="match_parent" android:layout_height="fill_parent" android:above="@+id/rl_commands"> <textview android:layout_width="match_parent" android:layout_height="wrap_content"/> </scrollview> <relativelayout android:layout_width="match_parent android:layout_height="24dp" android:id="@+id/rl_commands" android:alignparentbottom="true"> ...
Read more

Parse xml element into list in Python -

i've been trying days store list of coordinates list in python. i've tried minidom, xmlreader, , few others. i'm doing wrong, i'm on deadline , need help. <?xml version="1.0" encoding="utf-8"?> <kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/atom"> <document> <name>kmlfile</name> <style id="s_ylw-pushpin_hl"> <iconstyle> <scale>1.3</scale> <icon> <href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href> </icon> <hotspot x="20" y="2" xunits="pixels" yunits="pixels"/> </iconstyle> </style> <stylemap id="m_ylw-pushpin"> ...
Read more