Re: Help with query

[Christian Mack]

Anthony Mendoza wrote:
> 
> I'm hoping someone on this list can help me...
> 
> here's my table structure
> 
> tablename: userskills
> columns: username,skillid,skillratingid
> 
> sample data
> username,skillid,skillratingid
> user1,1,30
> user1,2,60
> user2,1,30
> 
> I need to write a query that will only return the usernames such that:
> skillid = 1 AND skillratingid = 30
> and
> skillid = 2 AND skillratingid = 60
> 
> here is a query that returns the correct names, but is obviosly not the right query:
> 
> select username from userskills where
> (skillid = 1 and skillratingid = 30) or (skillid = 2 and skillratingid = 60)
> 
> This returns empty (obviously)...
> 
> select username from userskills where
> (skillid = 1 and skillratingid = 30) and (skillid = 2 and skillratingid = 60)
> 
> This needs to scale -> N skillid/skillratingid combinations.
> 
> If I need to create a cross-ref table and do join, I'm open to that.  This is a new
> database and table structure so I have no problems re-arranging things.  I would, however,
> like to avoid creating a new table if possible.  The basic info about the table is that it
> stores the username of a person as well as the skills he has, with a rating.  I want to be
> able to search the table for a specific set of skills/rating combos for a user that may
> have 1 -> N combos being searched.
> 
> Any help would be appreciated.  Please let me know if you need any other additional
> info.
> -------------------------------------------------------------
> Anthony Mendoza

Hi Antony

The way to do such queries is an self join like this:
SELECT
	a.username
FROM
	userskills AS a
	, userskills AS b
WHERE
	a.username = b.username
	AND a.skillid = 1
	AND a.skillrating = 30
	AND b.skillid = 2
	AND b.skillrating = 60

But as you can see, for large N's this will be really slow (don't scale nicely).
Whith a cross-ref table you could only reduce the amount of data to process for this
query, because in the end you have to self join the cross-ref table.

Tschau
Christian

PS: Sorry for the late answer, I was really busy.