By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,774 Members | 1,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,774 IT Pros & Developers. It's quick & easy.

PHP MySQL question

P: n/a
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.
Jul 17 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

Jul 17 '05 #2

P: n/a
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 )
Jul 17 '05 #3

P: n/a
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

Jul 17 '05 #4

P: n/a
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
Jul 17 '05 #5

P: n/a

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

Jul 17 '05 #6

P: n/a
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/
Jul 17 '05 #7

P: n/a
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)
Jul 17 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.