Sarah Sarah - 20 days ago 5
MySQL Question

Fetch unique values from cross reference table

Right Now I have a cross reference table that looks like this:

Table job2pos
╔═══════════╦═════════════╗
║ job_id ║ position_id ║
╠═══════════╬═════════════╣
║ 1 ║ 10 ║
║ 2 ║ 10 ║
║ 2 ║ 12 ║
║ 3 ║ 11 ║
║ 3 ║ 13 ║
║ 4 ║ 10 ║
║ 5 ║ 13 ║
╚═══════════╩═════════════╝


I want to select all the
job_id
that have the
postion_id = 10
and
position_id = 12
, which would result in
job_id = 1,2,2,4
. But I only want
job_id = 2
to appear only once in my fetched result.

The code I tried are as follow:

$positions = ["10","12"];

$sql = "SELECT job2pos.job_id FROM job2pos
WHERE job2pos.pos_id IN (?,?)";

$stmt = $pdo->prepare($sql);

foreach ($positions as $key => &$val) {
$stmt->bindParam($key, $val, \PDO::PARAM_INT);
}

$stmt->execute();

var_dump($stmt->fetchAll(\PDO::FETCH_UNIQUE));


And the above attempt will produce the follow result:

array(3) {
[1]=> array(0) {}
[2]=> array(0) {}
[4]=> array(0) {}
}


So I did get unique values and the extra 2 is not repeating, but how do I make it so that the
job_id
does not appear as an index but instead an value in side an array?


  • P.S. If you can point out any unnecessary code in my code that will be great as well.


Answer

Special SELECT DISTINCT construct will bring distinct values only from your query. So you can rewrite your code as:

$postions = ["10","12"];
$sql = "SELECT DISTINCT job2pos.job_id FROM job2pos WHERE job2pos.pos_id IN (?,?)";
$stmt = $pdo->prepare($sql);

// btw you don't have to bind elements one by one
// you can pass array as an argument to execute:
$stmt->execute($positions);

print_r($stmt->fetchAll(\PDO::FETCH_ASSOC));
Comments