Skip to main content
9thReg
9thReg
Known Participant
June 28, 2011
Answered

help with inner join

  • June 28, 2011
  • 1 reply
  • 528 views

Hello all,

I'm trying to learn something new, and I'm having a little bit of difficulty.  I'm trying to learn about inner joins.  I've looked at countless books, tutorials, and everything else I can find, but I can't seem to figure this out.  Nothing I try works.

I've got tables that are related, and I'm trying to get the data to display.  I've got the database set up as follows:

movies (name of table) has the fields movie_id, movie_name, and movie_star_rating

family_rating (name of table) has the fields star_id, star_name, and star_file_name

in PHPMyAdmin, I've got movies.movie_star_rating internally related to family_rating.star_id

When I display the data, I am trying to pull from the movies table, but instead of having a number displayed, I would like to have family_rating.star_name displayed.  I've got the following code to do this:

$query="SELECT * FROM movies
  INNER JOIN family_rating ON movies.movie_star_rating=family_rating.star_id 
  WHERE movies.movie_name_series='$search'";
$result= mysql_query($query)or die (mysql_error());
while($row=mysql_fetch_array($result)){
// Display the data 
 echo $row['movies.movie_name'];
 echo $row['family_rating.star_file_name'];
}


Can anyone let me know what I'm doing wrong?  Thanks for your help.

This topic has been closed for replies.
Correct answer bregent

$query="SELECT * FROM movies, family_rating
   INNER JOIN family_rating ON movies.movie_star_rating=family_rating.star_id
   WHERE movies.movie_name_series='$search'";

OK, that SQL is not the same as your original post.

>>Not unique table/alias: 'family_rating'

This tells us where the error is. You are specifying both tables after the from clause. Try it again with your original query:

$query="SELECT * FROM movies
  INNER JOIN family_rating ON movies.movie_star_rating=family_rating.star_id
  WHERE movies.movie_name_series='$search'";

1 reply

B
bregent
Participating Frequently
June 28, 2011

>I'm trying to learn something new,

>and I'm having a little bit of difficulty.

>I'm trying to learn about inner joins.

Really? Inner joins are just regular joins - the most commonly used join in SQL. Are you thinking about something else?

>Can anyone let me know what I'm doing wrong?

What results are you getting? Show us some sample data from both tables.

9thReg
9thRegAuthor
Known Participant
June 28, 2011

Thank you for your reply.  It might be an issue about me thinking about something else, every tutorial and book I've read tells me that what I'm trying to do is an inner join (as opposed to a left join or right join).  But yes, I am trying to get information from another table to display when I query another table.  So, for some sample data:

This is the sample data for the table movies:

movie_idmovie_ratingparent_alertmovie_genre_onemovie_genre_twomovie_genre_threemovie_name
38722272822Test   Movie Entry
movie_name_seriesmovie_star_ratinglinkaliasartdescription
34testmovie.avi2coverart.jpgThis   is a test

This is the sample data for the table family_rating:
star_idstar_namestar_file_name
1
2One   Starone_star.png
3Two   Starstwo_star.png
4Three   Starsthree_star.png
5Four   Starsfour_star.png
11Five   Starsfive_star.png

The results that I'm getting with the code on the web page of:
$query="SELECT * FROM movies, family_rating
  INNER JOIN family_rating ON movies.movie_star_rating=family_rating.star_id 
  WHERE movies.movie_name_series='$search'";
$result= mysql_query($query)or die (mysql_error());
while($row=mysql_fetch_array($result)){
// Display the data
echo $row['movies.movie_name'];
echo $row['family_rating.star_file_name'];
}

is:
Not unique table/alias: 'family_rating'
What I'm trying to have as an output is (after a search is performed so the veriable being sent between pages is $search:
Test Movie Entry
One Star
[image of stars]
Obviously I'll ultimately be pulling information from other tables as well, but I'm starting with just two tables so I can learn how this works.  Again, I thank you for your help.
B
bregentCorrect answer
Participating Frequently
June 28, 2011
$query="SELECT * FROM movies, family_rating
   INNER JOIN family_rating ON movies.movie_star_rating=family_rating.star_id 
   WHERE movies.movie_name_series='$search'";
OK, that SQL is not the same as your original post.
>>Not unique table/alias: 'family_rating'
This tells us where the error is. You are specifying both tables after the from clause. Try it again with your original query:
$query="SELECT * FROM movies
  INNER JOIN family_rating ON movies.movie_star_rating=family_rating.star_id 
  WHERE movies.movie_name_series='$search'";

        
                                                

        

    


            

                                        

            
                        

                                                                                                            
            







        

                    

    
    





    


                                        

                                                        

            
        
    






                                    
Terms & ConditionsAccessibility statement

            
        



        
            
    
        
    
        



        
        
        


        


        
        
        
        
                                                        

                            
    

                    

  

    

      
        
      
    

    

      
        Using the community
      
      
        Terms of use
      
      
        Privacy
      
      
        Cookie preferences
      
      
        Do not sell or share my personal information
      
      
        ad choicesAdChoices