CODEWITHSUNDEEP
mysqlsqldatabaseinner-join
alionthego
alionthego

Reputation: 9703

how can I select multiple values from a inner join query with sql?

I would like to make a query to select a number of user emails based on an INNER JOIN. Presently I can get one value using the following:

"SELECT user_email FROM users INNER JOIN users_to_groups ON users.user_id = users_to_groups.user_id WHERE users_to_groups.group_id = ? AND users_to_groups.user_id = ?";

What I would like to do is to query the same group_id but with an array of users_to_goups_user_id's to get multiple results in one query. ie return multiple user emails for user's in a particular group. Is this possible?

Upvotes: 0

Views: 612

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider parameterization with the IN() clause after imploding an array of placemakers, ?. Below assumes using PHP's PDO.

$placemarkers = array_fill(0, count($user_id_array), "?");

$strSQL = "SELECT user_email FROM users u" .
          " INNER JOIN users_to_groups g ON u.user_id = g.user_id" .
          " WHERE g.group_id = ?" .
          "   AND g.user_id IN (" . implode(", ", $placemarkers) . ")";

$sth = $conn->prepare($strSQL);

$sth->bindParam(1, $group_id);    
for($i = 0; $i < count($user_id_array); $i++) {
   $sth->bindParam(i+2, $user_id_array[$i], PDO::PARAM_INT); 
}

$sth->execute();

Similarly with Python MySQL DB-API cursors:

placemarkers = ['%s' for i in user_id_array]
params_list = [group_id] + user_id_array

strSQL = "SELECT user_email FROM users u" + \
         " INNER JOIN users_to_groups g ON u.user_id = g.user_id" + \
         " WHERE g.group_id = %s" + \
         "   AND g.user_id IN ({})".format(", ".join(placemarkers))

cur = conn.cursor()
res = cur.execute(strSQL, params_list)

Upvotes: 1

Related Questions