need some help

[m4x3vo]

I have a table named user_ach and it has the columns user_id and ach_id.  How can I find the ach_id's that two user_id's have in common?

[rhodesa]

For users 1 and 2 it should be something like:

SELECT a.ach_id FROM user_ach a INNER JOIN user_ach b ON a.ach_id = b.ach_id AND a.user_id = 1 AND b.user_id = 2

[m4x3vo]

Nah didnt work  ???

[haku]

It should have - let's see your query.

[m4x3vo]

Ah it did work.

Is there a way to simplify my code, I want the query inside the while statement ($querytt) to be joined in the same query outside the statement ($queryt)...

both user_ach and ach can be joined on ach_id and id...

 

gah i know the code is messy xD

 

$queryt = "SELECT a.ach_id FROM user_ach a INNER JOIN user_ach b ON a.ach_id = b.ach_id AND a.user_id = 1 AND b.user_id = 15";
$resultt = mysql_query($queryt) or die(mysql_error());
while ($kkk = mysql_fetch_assoc($resultt)){
$lol = $kkk['ach_id'];
$querytt = "SELECT * FROM ach WHERE id = '$lol'";
$resulttt = mysql_query($querytt) or die(mysql_error());
$kkkk = mysql_fetch_assoc($resulttt);
$image = $kkkk['image'];
echo "<img src='$image'></img>"; }

 

[m4x3vo]

lol this is still bugging me xD

[rhodesa]

should be something like this:

$queryt = "SELECT c.image
    FROM user_ach a 
      INNER JOIN user_ach b 
        ON a.ach_id = b.ach_id 
      LEFT JOIN ach c
        ON a.ach_id = c.id
    WHERE a.user_id = 1 AND b.user_id = 15
";
$resultt = mysql_query($queryt) or die(mysql_error());
while($kkk = mysql_fetch_assoc($resultt)){
  $image = $kkk['image'];
  echo "<img src='$image'></img>";
}