Databases
StackOverflow2013
Postsdbo
POMySQL Correlated Subqueries
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL Correlated Subqueries
    primarykey
    data
    text
    <p>I've come to find out that the following SQL is not possible due to correlated subqueries within MySQL not having access to the outer query, when I run the below query I get the error of "player.playerid not found"</p> <pre><code>SELECT player.position, player.playerid, adp.average_draft_position FROM player, ( SELECT ((ad_p.mult + ad_p.pick_sum) / ad_p.total_mocks) AS average_draft_position FROM ( SELECT draft_c.total_mocks as total_mocks, ((draft_c.total_mocks - player_c.total_picks) * 252) AS mult, SUM(overall_pick) AS pick_sum FROM draft_history JOIN drafts ON drafts.id = draft_history.draftid, ( SELECT COUNT(drafts.id) AS total_mocks FROM drafts WHERE type = 3 ) AS draft_c, ( SELECT COUNT(draft_history.playerid) AS total_picks FROM draft_history WHERE draft_history.playerid = player.playerid ) AS player_c WHERE draft_history.playerid = player.playerid ) AS ad_p ) AS adp WHERE player.sportid = 1 AND player.position IN ('x', 'y', 'z') ORDER BY adp.average_draft_position ASC </code></pre> <p>I am attempting to use the following as a subquery in the above,</p> <pre><code>SELECT ((adp.mult + adp.pick_sum) / adp.total_mocks) AS average_draft_position FROM ( SELECT draft_c.total_mocks as total_mocks, ((draft_c.total_mocks - player_c.total_picks) * 252) AS mult, SUM(overall_pick) AS pick_sum FROM draft_history JOIN drafts ON drafts.id = draft_history.draftid, ( SELECT COUNT(id) AS total_mocks FROM drafts WHERE type = :type ) AS draft_c, ( SELECT COUNT(playerid) AS total_picks FROM draft_history WHERE playerid = :playerid ) AS player_c WHERE playerid = :playerid ) as adp </code></pre> <p>This will determine the average position a user has been picked throughout history, including cases when the user was not selected by applying a weight to their total sum, this one works.</p> <p>The first query above is attempting to take that and select all users that currently exist in the given positions get the average position and sort by it.</p> <p>I'm not so great at SQL so I could probably be doing this better and If I could explain this better please let me know.</p> <p>Problem solved with the given query,</p> <pre><code>SELECT position, playerid, ((((adp.total_mocks - adp.participated_mocks) * 252) + adp.pick_sum) / adp.total_mocks) AS average_draft_position FROM ( SELECT player.position, player.playerid, (SELECT COUNT(drafts.id) FROM drafts WHERE type = 3) as total_mocks, COUNT(draft_history.playerid) as participated_mocks, SUM(overall_pick) AS pick_sum FROM draft_history INNER JOIN drafts ON drafts.id = draft_history.draftid INNER JOIN player ON player.sportid = 1 AND player.position IN ('QB') AND draft_history.playerid = player.playerid GROUP BY player.position, player.playerid ) adp ORDER BY average_draft_position ASC </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNick
    1. USNick
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCan you provide some sample data (from all relevant tables) and then a sample result set (using "real" sample values that properly correlate to the sample table date)? The sample result set should be what you *want* from the query. Perhaps it would also help to include the results of your subquery that you said is working.
      singulars
      1. POMySQL Correlated Subqueries
      1. USChris Trahey
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload