SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help on mysql query (relational)

    Hi,

    I got some problem do a mysql query on these tables.
    I have 5 tables here with the relations like below.

    Code:
    Person - + - Eats    - Fruits
             |
             + - Watches - Movies
             
    Table: Person
    +---------+-----+
    |Person Id|Name |
    +---------+-----+
    |1        |John |
    |2        |Mary |
    |3        |Susan|
    +---------+-----+
    
    Table: Eats
    +---------+--------+
    |Person Id|Fruit Id|
    +---------+--------+
    |1        |1       |
    |1        |3       |
    |2        |3       |
    |3        |1       |
    |3        |2       |
    |3        |3       |
    +---------+--------+
    
    Table: Fruits
    +--------+----------+
    |Fruit Id|Fruit     |
    +--------+----------+
    |1       |Apple     |
    |2       |Orange    |
    |3       |Strawberry|
    +--------+----------+
    
    Table: Watches
    +-------------+---------+--------+
    |Relational Id|Person Id|Movie Id|
    +-------------+---------+--------+
    |1            |1        |2       |
    |2            |2        |1       |
    |3            |2        |2       |
    |4            |2        |3       |
    |5            |3        |2       |
    |6            |3        |3       |
    +-------------+---------+--------+
    
    Table: Movies
    +--------+------------+
    |Movie Id|Movie       |
    +--------+------------+
    |1       |Matrix      |
    |2       |Harry Potter|
    |3       |Monster Inc.|
    +--------+------------+
    
    Now, my question is - how to build a SQL query:-
    Find (a person's name, fruits eaten, movie watched)
    which (ate an apple and a strawberry only)
    and (watched the Harry Potter Movie only).
    
    And display the result like this
    +------+------------------+--------------+
    | Name | Fruit            | Movie        |
    +------+------------------+--------------+
    | John | Apple/Strawberry | Harry Potter |
    +------+------------------+--------------+
    Lots of thanks!!!
    --------------------
    Sorry for my english

  2. #2
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try something like:
    PHP Code:
    @mysql_query("select Name, Fruit, Movie from ".
    "Person, Fruits, Movies where ".
    "Person.Person Id=Eats.Person Id and ".
    "Eats.Fruit Id=Fruits.Fruit Id and ".
    "Eats.Fruit Id=1 and ".
    "Eats.Fruit Id=3 and ".
    "Person.Person Id=Watches.Person Id and ".
    "Watches.Movie Id=Movie.Movie Id and ".
    "Watches.Movie Id=2"); 
    If that's wrong then it's wrong -- I don't have a way of testing it. But just using joins like that will get the job done.
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  3. #3
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I believe accents are required around column identifier names that contain spaces or other special characters.

    For example, you would use "Eats.`Fruit Id`=Fruits.`Fruit Id`" instead of "Eats.Fruit Id=Fruits.Fruit Id"

    The mysql manual page on naming conventions is @ http://www.mysql.com/doc/L/e/Legal_names.html.

    - Marshall

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    thanks!

    Thanks for the reply.
    Sorry for the space in the column identifier names
    I mistype it. Assume There are no space in the names.
    But still return 0 result in my 2 attempts:-

    1)
    SELECT Name, Fruit, Movie
    FROM Person, Eats, Fruits, Watches, Movies
    WHERE Person.PersonId = Eats.PersonId AND
    Eats.FruitId = Fruits.FruitId AND
    Eats.FruitId = 1 AND
    Eats.FruitId = 3 AND
    Person.PersonId = Watches.PersonId AND
    Watches.MovieId = Movies.MovieId AND
    Watches.MovieId = 2

    2)
    SELECT Name, Fruit, Movie
    FROM Person, Eats, Fruits, Watches, Movies
    WHERE Person.PersonId = Eats.PersonId AND
    Eats.FruitId = Fruits.FruitId AND
    (Fruits.Fruit = 'Apple' AND
    Fruits.Fruit = 'Strawberry') AND
    Person.PersonId = Watches.PersonId AND
    Watches.MovieId = Movies.MovieId AND
    Movies.Movie = 'Harry Potter'

    I've try the IN LIST in the query but
    it seems like return as an OR result.

    3)
    SELECT Name, Fruit, Movie
    FROM Person, Eats, Fruits, Watches, Movies
    WHERE Person.PersonId = Eats.PersonId AND
    Eats.FruitId = Fruits.FruitId AND
    Fruits.Fruit IN ('Apple', 'Strawberry') AND
    Person.PersonId = Watches.PersonId AND
    Watches.MovieId = Movies.MovieId AND
    Movies.Movie = 'Harry Potter'

    How to get a result like this?
    Code:
    +-------+------------------+--------------+
    | Name  | Fruit            | Movie        |
    +-------+------------------+--------------+
    | John  | Apple/Strawberry | Harry Potter |
    | Susan | Apple/Strawberry | Harry Potter |
    +-------+------------------+--------------+
    Can LEFT JOIN solve this problem?

    I've includes a sample dump data for anyone who are so kind to try it. Lots of thanks in advance!
    Attached Files Attached Files

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    hm..

    I wonder the attachment won't upload.. oh well..
    here's the dump data ..

    Code:
    # phpMyAdmin MySQL-Dump
    # version 2.2.0
    # http://phpwizard.net/phpMyAdmin/
    # http://phpmyadmin.sourceforge.net/ (download page)
    #
    # Host: localhost
    # Generation Time: December 17, 2001, 12:17 am
    # Server version: 3.23.46
    # PHP Version: 4.0.6
    # Database : `test2`
    # --------------------------------------------------------
    
    #
    # Table structure for table `eats`
    #
    
    DROP TABLE IF EXISTS eats;
    CREATE TABLE eats (
      PersonId tinyint(3) NOT NULL,
      FruitId tinyint(3) NOT NULL,
      PRIMARY KEY  (PersonId,FruitId)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table `eats`
    #
    
    INSERT INTO eats VALUES (1,1),
    (1,3),
    (2,3),
    (3,1),
    (3,2),
    (3,3);
    # --------------------------------------------------------
    
    #
    # Table structure for table `fruits`
    #
    
    DROP TABLE IF EXISTS fruits;
    CREATE TABLE fruits (
      FruitId tinyint(3)  NOT NULL,
      Fruit text,
      PRIMARY KEY  (FruitId)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table `fruits`
    #
    
    INSERT INTO fruits VALUES (1,'Apple'),
    (2,'Orange'),
    (3,'Strawberry');
    # --------------------------------------------------------
    
    #
    # Table structure for table `movies`
    #
    
    DROP TABLE IF EXISTS movies;
    CREATE TABLE movies (
      MovieId tinyint(3) NOT NULL,
      Movie text,
      PRIMARY KEY  (MovieId)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table `movies`
    #
    
    INSERT INTO movies VALUES (1,'Matrix'),
    (2,'Harry Potter'),
    (3,'Monster Inc.');
    # --------------------------------------------------------
    
    #
    # Table structure for table `person`
    #
    
    DROP TABLE IF EXISTS person;
    CREATE TABLE person (
      PersonId int(3) NOT NULL,
      Name text,
      PRIMARY KEY  (PersonId)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table `person`
    #
    
    INSERT INTO person VALUES (1,'John'),
    (2,'Mary'),
    (3,'Susan');
    # --------------------------------------------------------
    
    #
    # Table structure for table `watches`
    #
    
    DROP TABLE IF EXISTS watches;
    CREATE TABLE watches (
      PersonId tinyint(3) NOT NULL,
      MovieId tinyint(3) NOT NULL,
      PRIMARY KEY  (PersonId,MovieId)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table `watches`
    #
    
    INSERT INTO watches VALUES (1,2),
    (2,1),
    (2,2),
    (2,3),
    (3,2),
    (3,3);

  6. #6
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi vkm,

    Try this query

    Code:
    SELECT name, fruit, movie FROM Person P, Fruits F, Movies M, Eats E, Watches W
    WHERE E.PersonID = P.PersonID AND E.FruitsID = F.FruitsID
    AND   W.PersonID = P.PersonID AND W.MovieID = M.MovieID
    AND FruitsID IN (1,3) AND MovieID = 2;
    NOTE: P,F,M,E,W are jsut alias for the tables.

    Paul


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •