I could not help to resist to create the tables and
try the hint, which is pretty close to the working
solution.
I just had to replace "order by user.id" with "group
by user.name skill.skill_name"
regards,
esv.
--- Parag Agrawal <[EMAIL PROTECTED]> wrote:
> 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]
>
>
Enrique Sanchez Vela
email: [EMAIL PROTECTED]
-----------------------------------------------------------------------------
It's often easier to fight for one's || We live in the outer space
principles than to live up to them || Rev. Kay Greenleaf
Adlai Stevenson ||
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
