Advanced Comparison query

[Anon]

Hi!

I really hope someone can help me with this one.

I have a table with two columns. One is called favorite band (BAND), and the other is the user the band belongs to (nick).

I want to make a query which makes a comparison with your own favorite band to all the other users favorite bands and hands back the similarity in percent.

If I have a about a thousand users or more it would be smart to have a function which finds and selects those users worth doing the comparison query on (eq. select my own bands and see what other users have them as their favorite bands), so I don't have to make a query for every user (which would have been VERY slow).

I don't have a clue how I shall do this without looping a LOTS of queries, hope you can.

// Johannes

[Anon]

select cast(100cast(count(t2.nick) as decimal)/
cast((select count() from t where nick = 'Lars') as decimal)
as decimal(4,2)) as percent,t2.nick

from t t1 join t t2 on t1.band = t2.band and t1.nick <> t2.nick
where t1.nick = 'Lars'
group by t2.nick
order by percent desc

This is to advanced for Mysql anyway, so if you are stuck with that you need to do it in two steps. First do the query

select count(*) from t where nick = 'Lars'

and use that value to build a new query.

[Anon]

i don't know if it will help you but ...

first i think you should use 3 tables like this:

bands(band_id, band_name)
users(user_id, nick)
fav_band(user_id, band_id)

then you have to generate a string with the id's of the favourite bands of the $given_user_id separated by comma looking like this:
'band_id1, band_id2, ...' (lets call it $band_string)

SELECT f.user_id, u.nick, count(f.user_id) count_of_matches
FROM fav_band f INNER JOIN users u ON f.user_id=u.user_id
WHERE f.band_id IN ($band_string) AND f.user_id<>$given_user_id
GROUP BY f.user_id
ORDER BY count_of_matches DESC

i think there's no need to explain how to get the percentage?!

[Anon]

Thanks!

It works just perfect!