This may not be elegant, but why not define a 3rd table proj_c containing proj and project_rsrc. This assumes that when you define a project you know how many resources are required. CREATE TABLE proj_c ( proj varchar(11) default NULL, project_rsrc INT default 0);
INSERT INTO proj_c VALUES ('ark',2),('cabin',1),('monalisa',2),('jeans',2); Then the sql becomes mysql> SELECT name, count(people.rsrc) AS person_rsrc, project_rsrc, project.proj -> FROM people -> LEFT JOIN project -> USING (rsrc) -> LEFT JOIN proj_c -> ON (project.proj = proj_c.proj) -> GROUP BY name, project.proj -> HAVING person_rsrc = project_rsrc -> ; +---------+-------------+--------------+----------+ | name | person_rsrc | project_rsrc | proj | +---------+-------------+--------------+----------+ | davinci | 2 | 2 | monalisa | | lincoln | 1 | 1 | cabin | | noah | 2 | 2 | ark | | noah | 1 | 1 | cabin | +---------+-------------+--------------+----------+ 4 rows in set (0.00 sec) -----Original Message----- From: Laszlo Thoth [mailto:[EMAIL PROTECTED] Sent: Friday, October 01, 2004 10:53 AM To: [EMAIL PROTECTED] Subject: matching people with projects via resources 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'),('mon alisa','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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]