Getting AVG rating from separate table help

[rokkstar]

Hello,

I'm hoping someone might be able to help me. I have a database with two tables, venue and reviews. I am returning all the results of the venue table using the following SQL query:
$query_venue = "SELECT * FROM venue WHERE County = '".$_GET['County']."'";

In the review table I have user reviews of the venues. I want to be able to add the avg rating given to the venue to list of returned records from the above query. The SQL i'm using is the following:
$query_rating = "SELECT ROUND(AVG(reviews.rating)) FROM reviews, venue WHERE reviews.venueName = venue.Name AND reviews.approved = '1' GROUP BY venue.Name";

My PHP to echo the records is as follows:

PHP Code:

<?php do { ?>
     <div class="resultblock">
          <h3><?php echo $row_venue['Name']; ?></h3>
          <div class="resultsaddress">                    
                        
                <ul class="resultsDisplay">
                     <li><?php echo $row_venue['Address1']; ?></li>
                            
                     <?php if ($totalRows_venue > 0) {  ?>
                     <li><?php echo $row_venue['Address2']; ?></li>
                     <?php } ?>
                              
                     <li><?php echo $row_venue['Town']; ?></li>
                     <li><?php echo $row_venue['County']; ?></li>
                     <li><?php echo $row_venue['Postcode']; ?></li>
               </ul>    
                    
          </div><!--/resultsaddress-->
                    
          <div class="resultsrating"><?php echo $row_rating['ROUND(AVG(reviews.rating))']; ?></div>
                    
          </div>  <!--/resultblock--> 
                    
<?php } while ($row_venue = mysql_fetch_assoc($venue)); ?>

However, the problem is that each record gets given the same rating regardless of whether it has one. I thought it might be easier to combine the SQL into one statement and came up with this:

$query_venue = "SELECT venue.ID, venue.Name, venue.Address1, venue.Address2, venue.Town, venue.County, venue.Postcode, ROUND(AVG(reviews.rating)) FROM venue, reviews WHERE County = '".$_GET['County']."' AND reviews.venueName = venue.Name GROUP BY venue.Name";

..but this only returns the records that have an associated rating in the reviews table.

Can anyone help me out with returning all records, and putting the rating of that record (if it has one)?

Any help would be greatly appreciated.

Cheers
Matt

[rokkstar]

Hello,

Does anyone have any idea with this at all? I'm tearing my hair out with this problem.

Cheers
Matt

[pantrygrazing]

venue right join review, then perform rollup on results of right join. there are other ways.