Hello.
What do you think about this:
SELECT u.name,
IFNULL(s.skill_name,'user doesn\'t have any
skill') AS SKILL,
(
SELECT COUNT(skill_id)
FROM user_skill us1
WHERE us1.skill_id = us.skill_id
) AS COUNT
FROM user u
LEFT JOIN user_skill us ON u.id = us.user_id
LEFT JOIN skill s ON us.skill_id = s.id
ORDER BY u.name;
+------+-----------------------------+-------+
| name | SKILL | COUNT |
+------+-----------------------------+-------+
| u1 | s1 | 2 |
| u1 | s3 | 1 |
| u2 | user doesn't have any skill | 0 |
| u3 | s1 | 2 |
| u3 | s2 | 1 |
+------+-----------------------------+-------+
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...
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
