0

I have two tables where one field links to an ID in the other table, and in this other table I would need to check if a value is valid or not. The tables look like this:

TABLE1
id - table2_id- deleted - user_id

TABLE2
id - is_valid

Now, I've tried using joins to no avail, but this is what I'm trying to accomplish:

Select a row (ID) from TABLE1 where deleted = 0, user_id = $id, and then I would need to take the table2_id of TABLE1 and go into TABLE2 on the row with the ID of table2_id and check if is_valid = 0.

I'd also need to limit results to 6 and have them chosen by random. Thanks in advance.

EDIT: Another way to visualize what I'm trying to accomplish:

$query = mysql_query("SELECT id, table2_id FROM table1 WHERE user_id = '$id' AND deleted = '0'");

        while ($row = mysql_fetch_array($query)) {   

            $table2_id = $row['table2_id'];
            $q = mysql_query("SELECT id FROM table2 WHERE id = '$table2_id' AND is_valid = '0'");

            if ($q) $final_ids[] = $row['id'];

        }
Tony Br.
  • 1
  • 1
  • For efficient random row selection out of a large table, see [Simple Random Samples from a (My)Sql database](http://stackoverflow.com/questions/249301/simple-random-samples-from-a-mysql-database). – outis Aug 29 '11 at 23:20

1 Answers1

3
    SELECT table1.id -- add other fields you might want
      FROM table1
INNER JOIN table2 
        ON table1.table2_id = table2.id
     WHERE user_id = $user_id
       AND deleted = 0
       AND is_valid = 0 -- do you really mean 0 here?
  ORDER BY RAND()
     LIMIT 6
NullUserException
  • 83,810
  • 28
  • 209
  • 234
  • Did some testing with this and I'm not sure if it's what I'm looking for. I also tried a 'normal' join of my own. Updated my edit aswell. – Tony Br. Aug 30 '11 at 00:27
  • @Tony So how is this incorrect? From what I read in your code, this query does the exact same thing, with the exception of picking at most 6 randoms rows as you've asked. – NullUserException Aug 30 '11 at 05:54