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 <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > To: <mysql@lists.mysql.com> > 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/[EMAIL PROTECTED] > > > -- > 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/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
