help on mysql query (relational)

[vkm]

Hi,

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

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

[Aes]

Try something like:

@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.

[Marshall]

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

[vkm]

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?

+-------+------------------+--------------+
| 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!

[vkm]

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

# 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);

[Paul S]

Hi vkm,

Try this query

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