Query Help

[jimmygle]

I am trying to populate an HTML table with information from multiple db tables, and when I add multiple AND clauses to the statement, I discover that it's pulling one row multiple times based on the AND clauses referring to other tables.  I basically know that it's just pulling it multiple times because it's meeting the criteria multiple times, I think.

 

I want to know if there's anyway in the syntax to tell it to limit certain AND clauses, or is there something else that can do it?  Here's the query, it's for a TV Show site I'm working on.  I think it's adding multiple entries because of the category part of the query, but I'm not sure.

 

mysql_query("SELECT s.id, s.show_name, c.cat_name FROM tv_shows s, tv_episodes e, tv_cats c WHERE s.id=e.show_id AND s.cat_id=c.id ORDER BY show_name ASC");

[fenway]

Sounds like you're missing a JOIN condition -- don't the use the comma operator, re-write as a proper JOIN with ON clauses and you'll see it.  EXPLAIN will help too.

[lemke411]

I would recommend writing out you queries like this:

 

$select = "SELECT s.id, s.show_name, c.cat_name

              FROM tv_shows AS s

              INNER JOIN tv_episodes AS e ON s.id=e.show_id

              INNER JOIN tv_cats AS c ON s.cat_id=c.id

              ORDER BY s.show_name ASC";

$result = mysql_query($select);

if (mysql_num_rows($results){

  while($row = mysql_fetch_row($result)){

     

  }

}

 

I used INNER JOINs but if the tables under the FROM may not have a record associated to the SELECT you may have to use a LEFT JOIN.  If were you I would read up on JOINs because you will use them a lot.   Good luck and have fun.