FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Help with a query[solved]





davidv
Imagine for now, I have two tables.

Code:
mysql> desc movies;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| movie_id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| title          | varchar(255)         | YES  |     | NULL    |                |
| genre1_id      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| genre2_id      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| year_published | year(4)              | YES  |     | NULL    |                |
| stars          | float                | YES  |     | NULL    |                |
| rating_id      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| date_added     | date                 | YES  |     | NULL    |                |
| viewed         | tinyint(1)           | YES  |     | NULL    |                |
| finished       | tinyint(1)           | YES  |     | NULL    |                |
| video_quality  | float                | YES  |     | NULL    |                |
| video_size     | float                | YES  |     | NULL    |                |
| runtime        | smallint(6)          | YES  |     | NULL    |                |
+----------------+----------------------+------+-----+---------+----------------+


and:
Code:

mysql> desc genres;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| genre_id | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| genre    | varchar(255)         | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+

As you can see there's a one to one relationship between genren_id and genre_id in genres. I know I can change it and make it a one to many with something like genre_sets and have a single field in movies point to a particular collection of genres but I wanna know if what I want to do using the current implementation is possible.

First,
Code:

mysql> select title, genre1_id, genre2_id from movies limit 1;
+------------+-----------+-----------+
| title      | genre1_id | genre2_id |
+------------+-----------+-----------+
| Zombieland |         1 |         4 |
+------------+-----------+-----------+
1 row in set (0.00 sec)


I want to replace the id with the actual genre. Doing this, obviously fails:

Code:
mysql> select m.title, g.genre gen1, g.genre gen2 from movies m inner join genres g on g.genre_id = m.genre1_id limit 1;
+------------+--------+--------+
| title      | gen1   | gen2   |
+------------+--------+--------+
| Zombieland | Action | Action |
+------------+--------+--------+
1 row in set (0.00 sec)


So how can I actually do it with this current implementation? I assume having:

Code:
... or g.genre_id = m.genre2_id...


will also return the same result due to short circuit evaluation. Anyway, thanks.
Fire Boar
davidv wrote:
As you can see there's a one to one relationship between genren_id and genre_id in genres. I know I can change it and make it a one to many with something like genre_sets and have a single field in movies point to a particular collection of genres but I wanna know if what I want to do using the current implementation is possible.


Actually what you have is a one-to-many relationship: one genre may have many associated films, and each film only has one genre1 (and one genre2). What you describe as "one-to-many" is something which is not possible directly in SQL: a single field can only ever have one value per row. To get a many-to-many relationship, you need an extra link table.

But anyway, putting that aside, the trick is to join the genre table twice, like this:

Code:
SELECT m.title, g1.genre gen1, g2.genre gen2 FROM movies m INNER JOIN genres g1 ON g1.genre_id = m.genre1_id INNER JOIN genres g2 ON g2.genre_id = m.genre2_id limit 1;


Thinking of it intuitively, this makes sense: each row in the movie table expands to a (movie, genre, genre) triple, so any data you could want must be in the cross join of movie and two genre tables.
davidv
Thanks for the help Fire Boar! I can always count on you.

Yes, of course it's a one-to-many relationship, what was I thinking when I said it was a one-to-one?! Also, I totally forgot you could join the same table over and over. Embarassed

Anyway, I made some changes to also include null values (assuming that there will never be a null value in the first genre field).

Code:
mysql> select m.title, g1.genre gen1, g2.genre gen2 from movies m inner join genres g1 on m.genre1_id = g1.genre_id left outer join genres g2 on g2.genre_id = m.genre2_id order by m.title limit 10;
+----------------------------+-----------+-----------+
| title                      | gen1      | gen2      |
+----------------------------+-----------+-----------+
| 10 Things I Hate About You | Comedy    | Romance   |
| 127 Hours                  | Adventure | Drama     |
| 21                         | Crime     | Drama     |
| 300                        | Action    | Fantasy   |
| 5 Days of War              | Action    | War       |
| 50/50                      | Comedy    | Drama     |
| 500 Days of Summer         | Drama     | Romance   |
| 9                          | Animation | Adventure |
| A Beautiful Mind           | Drama     | NULL      |
| A Clockwork Orange         | Crime     | Drama     |
+----------------------------+-----------+-----------+
10 rows in set (0.00 sec)
Related topics
Buy domain with e-gold, can you help me ? (Solved)
Beginning Programming... Help!
How to set a time with the host?
Some Query [solved]
Frih$ and Points problems
count input fields
How to Network 2 computers to a shared Printer
Cannot connect to my FTP Server.
Teaching Online
Help with mysql query
help me create a query
what is wrong with this query? Please please help!
[NOT Solved] Complicated mentions query
Google Ads help fund Spyware ?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.