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



Question:

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!

UPDATE:

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


Solution:1

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


Solution:2

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
Previous
Next Post »