StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQueries within queries counting rows to make a score based on categories chosen
text
Body
copied!<p>Is there a way this hand coded query could become dynamic?</p> <pre><code>SELECT master.id, (select count(0) as score1 from scores where scores.id = master.id AND scores.category = '1'), (select count(0) as score2 from scores where scores.id = master.id AND scores.category = '2'), (select count(0) as score3 from scores where scores.id = master.id AND scores.category = '3'), ( repeat for as many categories chosen by the current user ) score1+score2+score3 AS score FROM master ORDER BY score DESC LIMIT 1 </code></pre> <p>I know this syntax is incorrect.</p> <p>The effect I want is depending on a users chosen categories, I want to find a record. Each record is scored in another table.</p> <p>I want to be able to repeat the queries in brackets as many times as there are categories found in another database based on another id:</p> <pre><code>anotherid,category 1,1 1,2 1,3 2,2 2,3 3,1 3,2 3,3 </code></pre> <p>So if I passed '1' to the query above I'd like it to repeat the query in brackets for the result categories 1,2 and 3 (so three queries resulting in three scores adding up to an overall total).</p> <p>I have tried to ask this question before, but I think I over complicated things!</p> <p><strong>UPDATE:</strong></p> <p>I have just made this query - and I think it works. Anyone see any obvious mistakes?</p> <pre><code>SELECT users.id, users.url, ( SELECT SUM(scoretot.scr) FROM scoretot WHERE scoretot.id = users.id AND scoretot.category IN ( SELECT category FROM getprefs WHERE member = '2' ) ) AS score FROM users ORDER BY score DESC limit 1 </code></pre> <p>The value 2 will be dynamically created in the query in Perl (it will be the ID of the current user).</p> <p>I have two VIEWS</p> <pre><code>create view getprefs select `prefs`.`category` AS `category`,`prefs`.`member` AS `member` from `prefs` create view scoretot select count(`scores`.`ref`) AS `scr`,`scores`.`id` AS `id`,`scores`.`category` AS `category` from `scores` group by `scores`.`category` </code></pre> <p>And three tables:</p> <p>table users:</p> <pre><code>id,url 1,www.test.com 2,www.test2.com 3,www.test3.com </code></pre> <p>table scores:</p> <pre><code>id,category 1,1 1,1 1,2 1,2 1,3 1,3 1,3 2,2 3,1 3,3 3,3 3,3 3,2 </code></pre> <p>table prefs</p> <pre><code>member,category 1,1 1,2 1,3 2,1 3,1 3,3 </code></pre> <p>"think" that's it....</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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