PHP MySQL question

Hi everyone,

I have created a table with the following 3 columns

ID UserID SkillID
1 2 1
1 2 3
1 2 2
1 3 2

Is there a way to return all UserIDs that match SkillID=1 and SkillID=2 and
SkillID=3? (not OR)

Thank you
Maziar A.

re: PHP MySQL question

If 1, 2 and 3 are the _only_ allowed values for SkillID you can...

SELECT ID, UserID, count(SkillID) FROM <TableName> GROUP BY ID, UserID

Then those rows with a 3 in the third column are what you're looking
for.

May be wrong, my sql is a little rusty lately :D

re: PHP MySQL question

On 18 May 2005 09:09:43 -0700, "Samuel (lets dot monroe at gmail dot
com)" <lets.monroe@gmail.com> wrote:
[color=blue]
>If 1, 2 and 3 are the _only_ allowed values for SkillID you can...
>
>SELECT ID, UserID, count(SkillID) FROM <TableName> GROUP BY ID, UserID
>
>Then those rows with a 3 in the third column are what you're looking
>for.[/color]

Use HAVING to filter on the result of count():

SELECT ID, UserID, count(SkillID) FROM <TableName> GROUP BY ID, UserID
HAVING count(SkillID)=3

This also assumes a unique constraint on UserID and SkillID preventing
the same SkillID appearing multiple times for one UserID.

--
David ( @priz.co.uk )

re: PHP MySQL question

This would be a mysql question... try the mysql group..
I'm not sure, but I think this would be a self join

SELECT *
FROM table, table as table_copy
WHERE table.UserID = table_copy.UserID
AND table.SkillID = 1
AND table.SkillID = 2
AND table.SkillID = 3

or maybe something like:

SELECT UserID, SkillID,
GROUP_CONCAT(SkillID ORDER BY test_score DESC SEPARATOR ' ') as skills
FROM table
WHERE skills = '1 2 3'
GROUP BY UserID

re: PHP MySQL question

SELECT UserID FROM tablename WHERE UserID IN (SELECT UserID FROM tablename
WHERE SkillID=1) AND UserID IN (SELECT UserID FROM tablename WHERE
SkillID=2) AND UserID IN (SELECT UserID FROM tablename WHERE SkillID=3)

However, it may be more efficient for you to redesign your database.

ECRIA
http://www.ecria.com

re: PHP MySQL question

David Mackenzie ha escrito:
[color=blue]
> This also assumes a unique constraint on UserID and SkillID[/color]
preventing[color=blue]
> the same SkillID appearing multiple times for one UserID.[/color]

Yep, I assumed that from the fact that (as far as we know anyway) the
table has only three columns, so if ID and UserID can repeat SkillID
can't :)

re: PHP MySQL question

I noticed that Message-ID: <d6fqk2$kjl$1@murdoch.acc.Virginia.EDU> from
ECRIA Public Mail Buffer contained the following:
[color=blue]
>SELECT UserID FROM tablename WHERE UserID IN (SELECT UserID FROM tablename
>WHERE SkillID=1) AND UserID IN (SELECT UserID FROM tablename WHERE
>SkillID=2) AND UserID IN (SELECT UserID FROM tablename WHERE SkillID=3)[/color]


Can you do that in MySQL now?
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

re: PHP MySQL question

Geoff Berrow (blthecat@ckdog.co.uk) decided we needed to hear...[color=blue][color=green]
> >SELECT UserID FROM tablename WHERE UserID IN (SELECT UserID FROM tablename
> >WHERE SkillID=1) AND UserID IN (SELECT UserID FROM tablename WHERE
> >SkillID=2) AND UserID IN (SELECT UserID FROM tablename WHERE SkillID=3)[/color]
>
> Can you do that in MySQL now?
>[/color]
IIRC sub-selects came in at v4.1

--
Dave <dave@REMOVEbundook.com>
(Remove REMOVE for email address)