SQL JOIN获取项目的名称和所有标记

Question

this is my first ever JOIN clause and I did run into a problem. I want to echo all the necessary information for my items but I don't understand how to echo all the tags for one item, right now I get a list with duplicates of items but with different tags if more than one tag is assigned for an item. Any ideas? Better ways to do this is greatly appreciated as well.

$query = "SELECT categories.id, categories.category, spots.spot_name, spots.category_id, spots.description, spots.slug, districts.id, districts.district, tags.spot_id, tags.tag ".
 "FROM categories, spots, districts, tags ".
    "WHERE categories.id = spots.category_id AND districts.id = spots.district_id AND tags.spot_id = spots.id";

$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

while ($row = @mysql_fetch_array($result)){
echo '<tr><td style="background:#000; color:#ccc;" class="tooltip" title="'.$row["description"].'Tags: '.$row["tag"].'"><a style="color:#fff;" href="/'.$row["slug"].'">'.$row["spot_name"].'</a></td>
<td>'.$row["category"].'</td>
<td>'.$row["district"].'</td>
<td>****</td>
</tr>  
';
}

Thanks a million,

Anders

Answer 1

Change your Query and add left join like this: $query= "SELECT c.id, c.category, s.spot_name, s.category_id, s.description,". " s.slug, d.id, d.district, t.spot_id, t.tag". " FROM categories AS c, spots AS s, districts AS d". " JOIN tags AS t ON s.id = t.spot_id". " WHERE c.id = s.category_id AND d.id = s.district_id";