How to select values from an specific list of items

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

How to select values from an specific list of items

Post by emmbec »

Hi all, I am having problems trying to come up with a query that does the following. Let me try to explain.

I have these tables:

SKILLS
id
name
category_id

CATEGORIES
id
name

SKILL_CONFIGURATION
skill_id
category_id


What I want to do is get from the skill configuration table ALL of the configurations IF AND ONLY IF all of the categories are being used. For example:

ASsuming the following information is stored in the tables:
SKILLS
1, skill1
2, skill2
3, skill3
4,skill4

CATEGORIES
1,cat1
2,cat2
3,cat3


Having this configuration:
SKILL_CONFIGURATION
1,1
1,2
1,3

I expect to get three results, that I can do with a SELECT * FROM skill_configurations WHERE category_id IN(1,2,3)
However, if I have the following:
SKILL_CONFIGURATION
1,1
1,2
2,1
2,2
2,3

I want to get three rows (Only for skill 2 which has the three categories used) how can I do it? I'm lost :oops:

Thanks a lot!
QuantumTiger
Forum Newbie
Posts: 8
Joined: Thu Aug 27, 2009 11:17 am

Re: How to select values from an specific list of items

Post by QuantumTiger »

Not exactly what you want, but you can find that only skill 2 is in all three categories as follows.

Code: Select all

SELECT cs1.skillid
FROM skill_configuration cs1, skill_configuration cs2, skill_configuration cs3
WHERE cs1.skillid = cs2.skillid
AND cs1.skillid = cs3.skillid
AND cs1.catid =1
AND cs2.catid =2
AND cs3.catid= 3
Not particularly scaleable though, as the number of joins increases with each new category you wish to test against. Sure there is a more elegant solution, but it's not springing to mind!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How to select values from an specific list of items

Post by Eran »

That's an interesting set of requirements...
[sql]SELECT sc.skill_id,sc.category_id FROM skills_configuration AS scLEFT JOIN (   SELECT COUNT(skills_configuration.category_id) AS count,skill_id      FROM skills_configuration GROUP BY skill_id) AS sc_catnum ON sc_catnum.skill_id=sc.skill_idWHERE sc_catnum.count = (SELECT COUNT(categories.id) FROM categories)[/sql]
This counts the number of categories for each skill in the skills_configuration table, and then filters to only those who match the total count of categories in the category table
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Re: How to select values from an specific list of items

Post by emmbec »

Thanks a lot, that was an interesting use of the LEFT JOIN, I thought I could only left join to tables, but it does make sense to use an inner query in the left join to accomplish this... 8O

Thanks again :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to select values from an specific list of items

Post by VladSun »

Nice one, pytrin :)

My quick&dirty solution:
[sql]SELECT     skills_configuration.skill_idFROM     skills_configuration GROUP BY    skills_configuration.skill_idHAVING     count(DISTINCT skills_configuration.cat_id) = (SELECT COUNT(category.id) FROM category)[/sql]

It will show only skills_configuration.skill_ids, but I think 1) category ids are not of importance here (because it's all of them) and 2) it's easy to get the category_ids (either by having a select * from category or by using goup_concat/concat)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply