I am using a MySQL database, here's my situation: 

I need a select query to be able to get a list of projects that can be completed using N supplies, where N is an array of supplies. This list of projects must include all projects that can be completed using any or all of N supplies, but can not include any projects that require supplies not listed in N. *(e.g. in the make sketch project from the tables below paper has no substitute; however, pencil can be substituted by pen. If the query searches for projects that can be completed using pencil, pen, and pencil sharpener then 'make sketch' should not return as a project that can be completed, even though it uses some of the supplies listed)*

Additionally, some of the supplies required by certain projects can be substituted by other supplies; however, just because one project can use a substitute supply item does not mean that another project would work with that same substitute. *(e.g. in the sharpen pencil project below pen cannot be a substitute for pencil, however, for make drawing it can)*

These are my tables:

```
Projects
+----+---------------------+
| id |        name         |
+----+---------------------+
|  1 | make sketch         |
|  2 | sharpen pencil      |
|  3 | make paper airplane |
+----+---------------------+

Supplies
+----+------------------+
| id |       name       |
+----+------------------+
|  1 | paper            |
|  2 | pencil           |
|  3 | pen              |
|  4 | pencil sharpener |
+----+------------------+

ProjectSupplies
+----+-----------+------------+
| id | projectid |  supplyid  |
+----+-----------+------------+
|  1 |         1 |          1 |
|  2 |         1 |          2 |
|  3 |         2 |          2 |
|  4 |         2 |          4 |
|  5 |         3 |          1 |
+----+-----------+------------+

SubstituteSupplies
+-------------------+------------+
| projectsuppliesid |  supplyid  |
+-------------------+------------+
|                 2 |          3 |
+-------------------+------------+

```

The data isn't exhaustive by any means but you should get the point.

This is the query I came up with *previous to updating the database*(see update below), however, it breaks the rules because the query result includes projects that require paper simply because it `COUNT`'s both the supplyid and the substitute as two separate requirements rather than as simply fulfilling the same supply requirement.

```
SELECT projects.name FROM supplies
INNER JOIN projectsupplies ON supplyid = supplies.id OR substitute = supplies.id
INNER JOIN projects ON projects.id = projectid
WHERE supplies.id IN (2,3,4)
GROUP BY projects.name
HAVING COUNT(*) <= 3
ORDER BY projects.id

```

**Is there a way to turn this:**

```
INNER JOIN projectsupplies ON supplyid = supplies.id OR substitute = supplies.id

```

**into essentially this:**

```
INNER JOIN projectsupplies ON (supplies.id = supplyid) ? (supplies.id = supplyid) : (supplies.id = substitute)

```

**or something similar to that using an if statement or whatever in order to make the query result correct?**

**One problem that I had been experiencing is that the above query will return 'make sketch' as a valid project even though, as specified in the query, there is no paper.**

The end goal is to be able to accomplish this on a large scale with many projects and many supplies.

**UPDATE:** I found an issue in the design of my database that made it impossible to allow a supply to have more than one substitute. I corrected the problem to allow for many substitutes, and updated the tables above as necessary, so now the `SELECT` query above is no longer applicable. I still, however, need to accomplish the same goal that is mentioned at the top of this post