Page 1 of 1

How to select values from an specific list of items

Posted: Wed Feb 17, 2010 9:30 am
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!

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

Posted: Thu Feb 18, 2010 11:38 am
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!

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

Posted: Thu Feb 18, 2010 3:46 pm
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

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

Posted: Fri Feb 19, 2010 10:43 am
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 :)

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

Posted: Fri Feb 19, 2010 3:11 pm
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)