Try this out:
select user.name, skill.skill_name, count(user_skill1.skill_id)
from user , skill, user_skill , user_skill as user_skill1
where user.id=user_skill.user_id and user_skill.skill_id=skill.id and
skill.id = user_skill1.skill_id
order by user.id.
I think this would work.
--
Parag
B. Tech
IIIT, Hybd
select
On 7/31/05, Dean Karres <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have three simple tables:
>
> user
> {
> id int PRIMARY
> name varchar UNIQUE
> }
>
> skill
> {
> id int PRIMARY
> skill_name varchar UNIQUE
> }
>
> user_skill
> {
> user_id int
> skill_id int
> UNIQUE KEY id (user_id, skill_id)
> }
>
> Each user can have zero or more associated skills. So you might have
> these associations:
>
> User_1: Skill_1, Skill_3
> User_2: <no listed skills>
> User_3: Skill_2, Skill_1
>
> Assuming that is all the users and skills, I would like to produce a
> report that lists each user and then the list of skills each has (if
> any). Along with each skill I want to print the count of how many times
> this skill has been mapped to a user. Using the info above then
> something like:
>
> User_1
> Skill_1 (2)
> Skill_3 (1)
> User_2
> User_3
> Skill_1 (2)
> Skill_2 (1)
>
> The skill "usage counts" are in parens. I get that there will prolly be
> a join in order to pick up the fact that User_2 has no listed skills.
> What I'm not sure about is how to get the over all skill counts. Do I
> need to do the query that returns the user and skill list then go
> through the per user skills and do additional queries to get the skill
> counts?
>
> help?
>
> Dean...K...
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
When the going gets tough only the tough gets going
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
