MySQL Forums
Forum List  »  Newbie

Re: mysql one to many relationship?
Posted by: Phillip Ward
Date: January 05, 2015 06:56AM

MS Access is many, many things all jammed together into the same box.

MySQL is a relational database.
It's not going to go fancy "look-up" stuff for you because that's not its job. MySQL will store you data (very well) but you're going to have to use something else to build the User interface; PHP is well favoured around here.

Also, I'd suggest using an autoincrement key for your Movies table - you can easily get two movies with the same name and you don't want them getting mixed up.

(Warning: Air-code; probably contaminated by "full-blown" Oracle-thinking)
create table movie_list 
( ID     integer         not null  auto_increment 
, Title  varchar( 250 )  not null 
, primary key ( ID ) 
) ; 

create table user_list 
( ID    integer         not null  auto_increment 
, Name  varchar( 200 )  not null 
, primary key ( ID ) 
) ; 

create table watch_lists 
( User_ID   integer  not null 
, Movie_ID  integer  not null 
, primary key ( User_ID, Movie_ID ) 
, foreign key User_ID references user_list ( ID ) 
, foreign key Movie_ID references movie_list ( ID ) 
) ; 

insert into movie_list( Title ) 
values ( 'Jaws' ), ( 'Jaws 2' ), ( 'Jaws 3' ), ( 'Bambi' ) ; 

insert into user_list( Name ) 
values ( 'Fred' ), ( 'Wilma' ), ( 'Barney' ), ( 'Betty' ) ; 

insert into watch_lists 
values ( 1, 4 ), ( 2, 1 ), ( 3, 1 ), ( 4, 2 ) ; 

select  u.name 
,       m.Title 
from        watch_lists  w 
inner join  user_list  u 
      on    w.user_id = u.id 
inner join  movie_list  m 
      on    w.movie_id = m.id 
order by    u.id, m.id ; 

+--------+--------+
| Name   | Title  | 
+--------+--------+
| Fred   | Bambi  | 
| Wilma  | Jaws   | 
| Barney | Jaws   | 
| Betty  | Jaws 2 | 
+--------+--------+

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: mysql one to many relationship?
January 05, 2015 06:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.