It's not as elegant as I wanted I got it to work this way.... (I must be
getting tired)
create temporary table projects_need
SELECT proj
, count(rsrc) as rsrc_count
FROM project
group by proj;
CREATE temporary table suppliers_match
select p.proj
, s.name
, count(s.name) as sup_count
from project p
left join people s /* s for supplier */
on s.rsrc = p.rsrc
group by 1,2;
select pn.proj
, sm.name
, pn.rsrc_count
, sm.sup_count
, sm.sup_count/pn.rsrc_count * 100.0 pct_match
from projects_need pn
inner join suppliers_match sm
on sm.proj = pn.proj
order by proj
, pct_match desc;
drop temporary table projects_need, suppliers_match;
+----------+---------+------------+-----------+-----------+
| proj | name | rsrc_count | sup_count | pct_match |
+----------+---------+------------+-----------+-----------+
| ark | noah | 2 | 2 | 100.00 |
| ark | davinci | 2 | 1 | 50.00 |
| ark | lincoln | 2 | 1 | 50.00 |
| cabin | lincoln | 1 | 1 | 100.00 |
| cabin | noah | 1 | 1 | 100.00 |
| jeans | davinci | 2 | 1 | 50.00 |
| jeans | noah | 2 | 1 | 50.00 |
| jeans | NULL | 2 | 0 | 0.00 |
| monalisa | davinci | 2 | 2 | 100.00 |
| monalisa | noah | 2 | 1 | 50.00 |
+----------+---------+------------+-----------+-----------+
You can change the condition of the last query to join on
rsrc_count=sup_count so that you only get full supply list matches but I
thought that having a completion % was an interesting by-product of my
method.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Laszlo Thoth <[EMAIL PROTECTED]> wrote on 10/01/2004 11:53:23 AM:
> 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
bearbitrary:
> 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]
>
