Hi Laszlo,
This is sort of a butchery, and might be a little nicer with two queries and a temp table, but this works in mysql 4.1.3-beta (at least, it did for me).
SELECT A.name, B.proj FROM people as A, project as B WHERE A.rsrc=B.rsrc GROUP BY A.name, B.proj HAVING COUNT(*)=(SELECT COUNT(*) FROM project WHERE proj=B.proj);
This counts up the number of rows each (name,project) pair with resources in common and then checks to see if it's equal to the total number of resources for that project. This would be pretty slow on a really huge table (in the tens of thousands, maybe? I don't have a great sense for how it would scale), in which case you'd want to make a temporary table with all of the resource counts cached per project.
Hope that helps, Matt
Laszlo Thoth wrote:
I'm having difficulty constructing a query. I've got two kinds of information: a table of resources that various people have, and a table of resources that various projects need.
======================================================= CREATE TABLE `people` ( `name` varchar(11) default NULL, `rsrc` varchar(15) default NULL );
INSERT INTO `people` VALUES ('noah','wood'),('noah','canvas'),('lincoln','wood'),('davinci','canvas'),('davinci','paint');
CREATE TABLE `project` ( `proj` varchar(11) default NULL, `rsrc` varchar(15) default NULL );
INSERT INTO `project` VALUES ('ark','wood'),('ark','canvas'),('cabin','wood'),('monalisa','canvas'),('monalisa','paint'),('jeans','canvas'),('jeans','sewingmachine'); =======================================================
I need a query that will tell me which people have the resources required to complete a given project. Unfortunately all I can get are incomplete matches: I'm not sure how to express the concept of "fully satisfying the requirements" to MySQL.
Restructuring the tables is allowed: I'm not tied to the current schema, I just need to solve the problem. The only limit is that resources must be arbitrary: I can't use a SET to define resources because I might want to insert a new resource at some future point without redefining the column type.
I'm pretty sure this is a good starting point, but that's just matching resource to resource without excluding Lincoln from building an Ark (no canvas).
mysql> SELECT project.proj,project.rsrc,people.name FROM project LEFT JOIN people ON project.rsrc=people.rsrc; +----------+-------------+---------+ | proj | rsrc | name | +----------+-------------+---------+ | ark | wood | noah | | ark | wood | lincoln | | ark | canvas | noah | | ark | canvas | davinci | | cabin | wood | noah | | cabin | wood | lincoln | | monalisa | canvas | noah | | monalisa | canvas | davinci | | monalisa | paint | davinci | | jeans | canvas | noah | | jeans | canvas | davinci | | jeans | sewingmachi | NULL | +----------+-------------+---------+
It would also be sufficient but less optimal to solve a subset of this problem, where I only determine "who could complete this project" for a single project rather than trying to match all projects to all people in one query.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
