Tutorial :Queries within queries counting rows to make a score based on categories chosen


Is there a way this hand coded query could become dynamic?

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  

I know this syntax is incorrect.

The effect I want is depending on a users chosen categories, I want to find a record. Each record is scored in another table.

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:

anotherid,category  1,1  1,2  1,3  2,2  2,3  3,1  3,2  3,3  

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).

I have tried to ask this question before, but I think I over complicated things!


I have just made this query - and I think it works. Anyone see any obvious mistakes?

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  

The value 2 will be dynamically created in the query in Perl (it will be the ID of the current user).

I have two VIEWS

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`  

And three tables:

table users:

id,url  1,www.test.com  2,www.test2.com  3,www.test3.com  

table scores:

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  

table prefs

member,category  1,1  1,2  1,3  2,1  3,1  3,3  

"think" that's it....


Yes, basically you want to code a pivot table. First, an easier way to do it, with less typing and less overhead would be:

SELECT    master.id  ,         SUM(IF(s.category='1',1,0))   cat1  ,         SUM(IF(s.category='2',1,0))   cat2  ,         SUM(1)                        total  FROM      master m  LEFT JOIN scores s  ON        m.id = s.id  GROUP BY  master.id  

(here's an exaplanation and background: http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html)

The trick is of course to generate the columns dynamically. Turns out, you can do this with a stored procedure. Here's an example of how you can do that: http://www.futhark.ch/mysql/106.html

If you are using MySQL proxy, you can also take a look at http://forge.mysql.com/wiki/ProxyCookbook


for better readability I would recommend that you create separate views for your subqueries.

CREATE VIEW v_scores_category1 AS       SELECT count(0) AS score1      FROM scores where scores.id = mASter.id AND scores.category = '1'    CREATE VIEW v_scores_category2 AS      SELECT count(0) AS score2      FROM scores where scores.id = id AND scores.category = '2'  

and then ...

SELECT mASter.id,  (SELECT score1 FROM v_scores_category1),  (SELECT score2 FROM v_scores_category2),  score1+score2 AS score FROM mASter ORDER BY score DESC LIMIT 1  

BTW: I know this syntax is incorrect ;) ...

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »