php，mysql - 选择具有多个值的列的行

Question

I have the following kind of setup (a bit oversimplified):

An array (php): array('a', 'b', 'c');

Table 1: id with values (1), (2), (3), (others).

Table 2: id, idT1,value with values (1, 1, 'a'), (2, 1, 'b'), (3, 1, 'c'), (4, 2, 'c'), (5, 77, 'w') (others).

I need to select rows from table 1, that have correspondent in table 2 with ALL the values in the array (in this case the row with id col = 1).

A way to do this would be something like (ignore possible typos pls)

 <?php
      $query = "SELECT * FROM `table1` WHERE `id` IN ";
      $subqr = "";
      foreach ($arr as $a) {
          if (!$subqr)
              $subqr = "(SELECT `idT1` FROM `table2` WHERE `value` = '$a')";
          else
              $subqr .= " AND `id` IN (SELECT `idT1` FROM `table2` WHERE `value` = '$a')";
      }

      ExecuteQuery($query.$subqr); // Where ExecuteQuery gets query as param.

Now my problem is that... I'm not exactly comfortable with the solution... I mean... a foreach that adds subqueries is likely not to be the best approach. If the array has 9999 elements... 9999 subqueries would just kill mysql. Also bear in mind that this is oversimplified.

Any other ideas how to do this?

Answer 1

Correct me if I'm wrong, you want to select all the records from Table 1 which has it's ID on Table 2 on field idT1. If that's the case you can try

SELECT id
FROM table1
LEFT OUTER JOIN table2
ON table1.id=table2.idT1;

EDIT:

SELECT * 
FROM table1 
INNER JOIN table2 
ON table1.id=table2.idT1
WHERE table2.value IN ('a','b','c')
GROUP BY table1.id
HAVING COUNT(DISTINCT table2.value) = 3

so first, you need to use php implode the array for the line

WHERE table2.value IN ('a','b','c')

then php count for this line

HAVING COUNT(DISTINCT table2.value) = 3

that would replace 3