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!

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 i开发者_开发百科n 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 ;) ...