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]
