PHP MySQL question

[Maziar Aflatoun]

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.

[Samuel (lets dot monroe at gmail dot com)]

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

[David Mackenzie]

On 18 May 2005 09:09:43 -0700, "Samuel (lets dot monroe at gmail dot
com)" <le*********@gmail.com> wrote:

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.

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 )

[BKDotCom]

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

[ECRIA Public Mail Buffer]

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

[Samuel (lets dot monroe at gmail dot com)]

David Mackenzie ha escrito:

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

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 :)

[Geoff Berrow]

I noticed that Message-ID: <d6**********@murdoch.acc.Virginia.EDU> from
ECRIA Public Mail Buffer contained the following:

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)

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/

[Dave]

Geoff Berrow (bl******@ckdog.co.uk) decided we needed to hear...

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)

Can you do that in MySQL now?

IIRC sub-selects came in at v4.1

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