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

Answer 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

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