List:General Discussion« Previous MessageNext Message »
From:sheeri kritzer Date:February 10 2006 2:03pm
Subject:Re: Multiple many-to-many SELECT
View as plain text  
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=1
>
>
> --
> 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=1
>
>
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