List:General Discussion« Previous MessageNext Message »
From:Jessica Yazbek Date:February 10 2006 8:49pm
Subject:Re: Multiple many-to-many SELECT
View as plain text  
Thanks for the replies guys. I am self-taught and did do a fair  
amount of reading up on database design before I created my database.  
I'm not sure, however, that I explained myself clearly enough; the  
table schemas I provided were supposed to show that I have a do  
relating table between each many-to-many relationship.

The tables are as follows:

movies
director
producer
director_movies (relates director.id to movies.id)
producer_movies (relates producer.id to movies.id)

I'm hoping for a way to select movies.*, director.* and producer.*  
using only the movies.id field. I have sucessfully done it using  
INNER JOINs if there is a both a related director and producer for  
the movie I am selecting, however, of course the INNER JOIN query  
returns zero results if either a director or producer is missing for  
a movie (which could very well be the case on a good number of the  
movies). When I tried changing INNER JOIN to LEFT JOIN in my query, I  
received a syntax error that I could not figure out.

Right now I have resorted to doing the query using separate SELECT  
statements for each table, which I don't believe is ideal because it  
involves a round-trip between the web server and the database server  
for each related table. I would prefer to do it with one SELECT  
statement if possible. Unless of course, I am mistaken,  and several  
small simple SELECT statements would be faster and less load on the  
server than a single more complicated one. The database schema I  
described above is a simplified version of the real database - I  
actually have 8 related tables (and corresponding relating tables for  
each of those). I assume doing 8 separate SELECTs would be more load  
on the server than one.

Any ideas?

Many Thanks,
Jessica

On Feb 10, 2006, at 6:03 AM, sheeri kritzer wrote:

> This is where a theoretical education is great. . .Lots of us are
> self-taught, some of us have had theoretical stuff.  I hated
> theoretical stuff in college and grad school, because I just wanted to
> sit and make something productive.  I'm coming to realize that that
> kind of education is invaluable. . .just having gone through
> relational algebra and calculus, and seeing how SQL is different from
> that stuff....
>
> anyway.  I think a small bit of reading on the Entity-Relationship
> model (ER model) will help.  A good page is:
>
> http://www.utexas.edu/its/windows/database/datamodeling/dm/ 
> erintro.html
>
> Note the link at the bottom of that page to "Data Modeling as part of
> Database Design".  That whole site is really good, actually.
>
> -Sheeri
>
> On 2/9/06, Rhino <rhino1@stripped> wrote:
>> Jessica,
>>
>> It think your first mistake is that you are trying to implement  
>> many-to-many
>> relationships directly. Although it is theoretically possible to  
>> do so, I've
>> never seen anyone do it myself. I believe the normal approach is  
>> to break
>> each many-to-many relationship into two one-to-many relationships  
>> with an
>> association table (sometimes called an intersection table) in the  
>> middle.
>> Then, you join the tables together as needed, using inner, left or  
>> right
>> joins as appropriate for your situation.
>>
>> I spent several hours detailing most of this in emails on this  
>> list several
>> months back in response to a similar question and I _really_ don't  
>> want to
>> go through all that typing again :-) Instead, I'm going to refer  
>> you to the
>> MySQL archives. If you go to this page -
>> http://lists.mysql.com/mysql/171636 - you will see the beginning  
>> of a thread
>> where I was one of the participants. We were discussing many-to-many
>> designs. You probably don't need to read the whole thread but  
>> certainly read
>> the first few posts in the thread, especially
>> http://lists.mysql.com/mysql/171645, which is where I first  
>> describe how to
>> implement a many-to-many relationship between members (of a video  
>> club) and
>> the titles of the movies they rent.
>>
>> That should get you started. You can read more in that thread to  
>> hear more
>> of the pros and cons of the issue but you may find this thread  
>> wanders
>> somewhat and is completely relevant to your concerns.
>>
>> Another thing you could try, to see other discussions of many-to-many
>> implementations, is to go to http://lists.mysql.com/ and fill in  
>> the search
>> box as follows:
>> Search mailing lists for: intersection table
>> Within: MySQL General Discussion
>> Matching: all of the words
>> since: the beginning
>>
>> You may find that other people explain the idea more clearly or  
>> convincingly
>> than I do ;-)
>>
>> I have to dash but if you have followup questions, post them in  
>> the list and
>> I, or someone else, will likely be able to answer.
>>
>> --
>> Rhino
>>
>>
>> ----- Original Message -----
>> From: "Jessica Yazbek" <jessica@stripped>
>> To: <mysql@stripped>
>> Sent: Thursday, February 09, 2006 5:28 PM
>> Subject: Multiple many-to-many SELECT
>>
>>
>> Hello,
>>
>> I apologize if this is a common question; I have been working with it
>> and googling for days, and can't seem to find anyone who has been
>> trying to do the same thing that I am. Maybe I'm using the wrong
>> keywords. In any event, I am desperate for help. Here is my problem:
>>
>> I have a database with several tables related on a many-to-many
>> basis. Here is a simplified description:
>>
>> TABLE: movies
>> +---------------------+---------+------+-----+---------+-------+
>> | Field               | Type    | Null | Key | Default | Extra |
>> +---------------------+---------+------+-----+---------+-------+
>> | id                  | int(11) |      | PRI | 0       |       |
>> | catalog_description | text    | YES  |     | NULL    |       |
>> | title               | text    | YES  |     | NULL    |       |
>> | website_url         | text    | YES  |     | NULL    |       |
>> +---------------------+---------+------+-----+---------+-------+
>>
>> TABLE: director
>> +---------------------+---------+------+-----+---------
>> +----------------+
>> | Field               | Type    | Null | Key | Default |
>> Extra          |
>> +---------------------+---------+------+-----+---------
>> +----------------+
>> | id                  | int(11) |      | UNI | NULL    |
>> auto_increment |
>> | director_first_name | text    |      | PRI |
>> |                |
>> | director_last_name  | text    |      | PRI |
>> |                |
>> +---------------------+---------+------+-----+---------
>> +----------------+
>>
>> TABLE: producer
>> +---------------------+---------+------+-----+---------
>> +----------------+
>> | Field               | Type    | Null | Key | Default |
>> Extra          |
>> +---------------------+---------+------+-----+---------
>> +----------------+
>> | id                  | int(11) |      | UNI | NULL    |
>> auto_increment |
>> | producer_first_name | text    |      | PRI |
>> |                |
>> | producer_last_name  | text    |      | PRI |
>> |                |
>> +---------------------+---------+------+-----+---------
>> +----------------+
>>
>> TABLE: director_movies
>> +-------------+---------+------+-----+---------+-------+
>> | Field       | Type    | Null | Key | Default | Extra |
>> +-------------+---------+------+-----+---------+-------+
>> | movie_id    | int(11) |      |     | 0       |       |
>> | director_id | int(11) |      |     | 0       |       |
>> +-------------+---------+------+-----+---------+-------+
>>
>> TABLE: producer_movies
>> +-------------+---------+------+-----+---------+-------+
>> | Field       | Type    | Null | Key | Default | Extra |
>> +-------------+---------+------+-----+---------+-------+
>> | movie_id    | int(11) |      |     | 0       |       |
>> | producer_id | int(11) |      |     | 0       |       |
>> +-------------+---------+------+-----+---------+-------+
>>
>> There are actually several more related tables and fields, but I
>> think this is enough to give an idea of what I have. What I am trying
>> to do is SELECT all the information about a movie (all fields from
>> the movies table, plus director_first_name, director_last_name,
>> producer_first_name, producer_last_name) based on the id from the
>> movies table. The only problem is that there may not be a producer
>> and/or a director listed for a given movie, in that case, I need
>> those columns to be absent from the results (ideally), or at least
>> return as NULL. I was able to write a select statement that works
>> exactly as I want it in the cases where there is both a director and
>> a producer listed for the movie:
>>
>> SELECT *
>> FROM movies
>> INNER JOIN (director INNER JOIN director_movies
>> ON director_movies.director_id = director.id
>> AND director_movies.movie_id=movies.id)
>> INNER JOIN (country INNER JOIN country_movies
>> ON country_movies.country_id = country.id
>> AND country_movies.movie_id=movies.id)
>> INNER JOIN (producer INNER JOIN producer_movies
>> ON producer_movies.producer_id = producer.id
>> AND producer_movies.movie_id=movies.id)
>> WHERE movies.id = 123
>>
>> However, if there is no producer and/or director listed for the movie
>> id given, then the query returns 0 rows. I thought I might need to
>> change my JOINs to be LEFT JOINs insead of INNER, but when I change
>> the statement so that it reads as follows:
>>
>> SELECT *
>> FROM movies
>> LEFT JOIN (director INNER JOIN director_movies
>> ON director_movies.director_id = director.id
>> AND director_movies.movie_id=movies.id)
>> LEFT JOIN (country INNER JOIN country_movies
>> ON country_movies.country_id = country.id
>> AND country_movies.movie_id=movies.id)
>> LEFT JOIN (producer INNER JOIN producer_movies
>> ON producer_movies.producer_id = producer.id
>> AND producer_movies.movie_id=movies.id)
>> WHERE movies.id = 123
>>
>> I get a syntax error #1064 near my WHERE clause, and I can't figure
>> out what's causing it. I am new to understanding JOIN clause syntax,
>> so please excuse me if it's obvious.
>>
>> I'm terribly sorry for such a long post. If anyone has any clue how I
>> can accomplish what I need to do, I will be so happy. As it is, I've
>> been on the verge of tears over this problem for way too long!
>>
>> Many thanks in advance,
>> Jessica Yazbek
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql? 
>> unsub=rhino1@stripped
>>
>>
>> --
>> No virus found in this incoming message.
>> Checked by AVG Free Edition.
>> Version: 7.1.375 / Virus Database: 267.15.3/254 - Release Date:  
>> 08/02/2006
>>
>>
>>
>>
>> --
>> No virus found in this outgoing message.
>> Checked by AVG Free Edition.
>> Version: 7.1.375 / Virus Database: 267.15.3/254 - Release Date:  
>> 08/02/2006
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql? 
>> unsub=awfief@stripped
>>
>>
>

Thread
Multiple many-to-many SELECTJessica Yazbek9 Feb
  • Re: Multiple many-to-many SELECTRhino10 Feb
    • Re: Multiple many-to-many SELECTsheeri kritzer10 Feb
      • Re: Multiple many-to-many SELECTJessica Yazbek10 Feb
Re: Multiple many-to-many SELECTJessica Yazbek12 Feb
Re: Multiple many-to-many SELECTPeter Brawley12 Feb
  • Re: Multiple many-to-many SELECTSGreen13 Feb
    • Re: Multiple many-to-many SELECTSGreen13 Feb