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]
