mysql - SQL Select with multiple search parameters using joins and subqueries -

-

i have spent hours searching answer problem without satisfying results.

i want select 1 query players, villages , alliances -tables , date , population histories table.

selection must filtered following rules:

  1. select latest information date.
  2. select if player has <= number of villages @ moment.

  3. select if total population of player's villages <= @ moment

    1. and 3. ones causing head hurt. how add query?

here current query:

select players.name player,         players.uid uid,         players.tid,         villages.name village,         villages.vid vid,         villages.fid fid,         alliances.name alliance,         alliances.aid aid,         sqrt( pow( least(abs($xcoord - villages.x),                         400-abs($xcoord - villages.x)), 2 ) +               pow( least(abs($ycoord - villages.y),                         400-abs($ycoord - villages.y)), 2 ) ) distance histories left join players on players.uid = histories.uid left join villages on villages.vid = histories.vid left join alliances on alliances.aid = histories.aid left join histories h2         on ( histories.vid = h2.vid , histories.idhistory < h2.idhistory ) h2.vid null   , histories.uid != $uid   , sqrt( pow(least(abs($xcoord - villages.x),                       400-abs($xcoord - villages.x)), 2 ) +              pow(least(abs($ycoord - villages.y),                       400-abs($ycoord - villages.y)), 2 ) ) < $rad order distance

notice: xcoord , ycoord posted search form.

example output:

player| village | alliance | distance  p1    | v1      | a1       | 1   p2    | v4      | a2       | 2   p1    | v2      | a1       | 3   p1    | v3      | a1       | 4   p2    | v5      | a2       | 5

thank in advance helping. :)

this query can find players have less 2 villages. cant put original query , together. possible?

select b.*, count(b.uid) hasvillages  histories b  b.vid in (select a.vid villages a)  group  b.uid having  count(b.uid) < 2

here link sqlfiddle

here link picture of database eer diagram

after 1 week of try-outs have found answer. query can use following search parameters:

  1. find latest rows date
  2. find rows limiting number of villages player has.
  3. find rows limiting total population of villages player has.
  4. find rows calculating distance.
  5. exclude players or alliances selection.

here query

select players.name player, players.uid uid, players.tid,  villages.name village, villages.vid vid, villages.fid fid,  alliances.name alliance, alliances.aid aid,  sqrt( pow( least(abs(100 - villages.x),400-abs(100 - villages.x)), 2 ) +  pow( least(abs(100 - villages.y),400-abs(100 - villages.y)), 2 ) ) distance             histories             left join players on players.uid = histories.uid             left join villages on villages.vid = histories.vid             left join alliances on alliances.aid = histories.aid             histories.uid in                       (select b.uid histories b                       (b.vid in (select a.vid villages a) , b.date                       in (select max(date) histories))                       group b.uid having count(b.uid) < 4 ,                       sum(b.population) < 2000)        , histories.uid != 1        , histories.date in (select max(date) histories)        , sqrt( pow( least(abs(100 - villages.x),400-abs(100 - villages.x)),2)+         pow( least(abs(100 - villages.y),400-abs(100 - villages.y)), 2 ) ) < 200        order distance

Comments

Post a Comment

Popular posts from this blog

javascript - DIV "hiding" when changing dropdown value -

-
this related previous question asked: hide / show multiple divs i have code in place previous question , seems work ok apart when change value in dropdown "after" ticket selection made. i have number of javascrpts in place wondering if there clash somewhere? first bit of code in head of document. <head> <script type="text/javascript"> $(function() { $('.cat_dropdown').change(function() { $('#paymethod').toggle($(this).val() >= 2); }); }); </script> <script type="text/javascript"> $(document).ready(function () { $(".paymentmethod").click(function () { $(".paymentinfo").hide(); switch ($(this).val()) { case "credit card authorisation": $("#pay0").show("slow"); break; ...
Read more

Does Firefox offer AppleScript support to get URL of windows? -

-
i trying write applescript that, among other things, gets url of every open webpage in firefox. in safari (and chrome), done simply: tell application "safari" return url of every tab in every window however, seems me firefox offers no real applescript support, such getting url of tab or window. when google terms "firefox" , "applescript" together, firefox bug requests asking applescript support restored, last updated in 2010 or 2011 (like this , this ). am right in thinking, then, firefox no longer offers proper applescript support? realize there semi-workarounds, such simulating key-commands in applescript, aren't practical purposes. to see of applescript commands firefox responds to, launch applescript editor, select menu file > open dictionary... , , choose firefox application. you'll find you're expecting: firefox doesn't offer useful applescript commands.
Read more

android - How to install packaged app on Firefox for mobile? -

-
how test-install packaged (zip) app on fennec? device: physical android phone or android emulator, don't care. install mozilla-apk-cli using npm: npm install -g mozilla-apk-cli use generate "debuggable" apk app either source directory or url mini-manifest: mozilla-apk-cli /path/to/source/dir/ arbitrary-name.apk mozilla-apk-cli http://example.com/path/to/mini/manifest.webapp arbitrary-name.apk (context-click > inspect element on "free" button in marketplace discover mini-manifest url app in marketplace.) install apk on android device: adb install -r arbitrary-name.apk launch app on device. notification area notification port remote debugger server listening on. forward port on desktop, f.e. if port 12345: adb forward tcp:12345 tcp:12345 go web developer > connect… in firefox on desktop , connect localhost @ forwarded port. commence debugging! notes: use nightly builds of fennec best experience. bug 929382 tracks ...
Read more