是否可以在单个查询中将多对多关系与一对多关系组合在一起？

Question

I have a MOVIE table that includes a many to many relationship with a genre as well as a review table that has a one to many relationship. AKA, a movie can have many genres and a genre can have many movies. A movie can have many reviews.

My problem is that as I'm trying to display all of the data from the database I want to display the title, genres associated, and then all of the reviews. I can't seem to do this with just the query, so I started to add my own logic, but that didn't work for me either. I keep thinking that there must be some way to combine the logic into one query.

Here is what I have so far:

$query2 = "SELECT * FROM movie m 
JOIN moviegenre mg ON m.id = mg.movieid 
JOIN genre g ON g.id = mg.genreid
JOIN review r ON r.movieid = m.id
WHERE m.id = mg.movieid";

$title = "starting";
$display = true;
foreach ($db->query($query2) as $row2)
{
    if ($title != $row2['title']) 
    {
        $title = $row2['title'];
        echo '<h2 id="title">' . $row2['title'] . " (" . $row2['year'] 
             . ')<br/><hr/></h2><h4> <u>- Genres - </u><br/>';
        $display = false;
    }
    echo $row2['name'] . '  <br/>  ';

    if ($display == false)
    {
        $display = true;
        echo "<h4 id=\"rating\">" . $row2['rating'] . " stars<br/> " . $row2['subject'] . "<br/>" . "<h5 id=\"rating\">" . $row2['content'] . "</h5></h4>";
    }
} 
echo '</h4>';

And here is the result. Note, it can only include one review and the formatting is pretty ugly. I know that this is really ugly code, it's just been me trying to fix errors in roundabout ways.

Raiders of the Lost Ark (1981)

• Genres - Action 5 stars Great Movie

This movie never gets old

Adventure

Answer 1

I found an answer while reading a few days later. I learned about joins and realized that they were exactly what I was looking for.