Question creating 1 to many relationship in mysql?

[AsthreA]

I'm new to mysql and currently using c# visual studio 2012.. I need a One to many ralation tutorial.. like for example I made a table called MovieList with 1 column name TitleMovie and now I want to create another table called acc1_MyWatchlist and inside of it was also a TitleMovie..my question is I want insert a value in acc1_mywatchlist but only the available values in Movielist's TitleMovie and If I tried to create another table acc2_mywaychlist it should be the same as the first table and if I tried to edit some values in the MovieList table it should be affect all the table that has the same column TitleMovie....

in Access database I just use the lookup wizard but ofcourse mysql is differnt so can you give me some examples of this query..

 

[jmcilhinney]

To be clear, you're asking how to set up the tables in MySQL as opposed to how to use them in C#, correct?

 

[AsthreA]

yes and it would be grateful if you give me some query of that example I've given so i can use it as reference on making an inventory system using c#

 

[jmcilhinney]

Firstly, the movie information should only be stored in one place, i.e. you don't duplicate information from the MovieList table (that table really should be just named Movie and the column named Title) in the acc1_MyWatchlist table(what is the acc1_My part for). For instance, let's say that you have a Movie table with MovieId and Title columns, a User table with a UserId column and a WatchList table that stores a list of the movies watched by each user. The WatchList table should only have UserId and MovieId columns but not a Title column. If you want to get the title of each movie watched by a particular user then you join the WatchList table with the Movie table and filter by UserId:

SELECT Title
FROM WatchList INNER JOIN Movie
ON WatchList.MovieId = Movie.MovieId
WHERE WatchList.UserId = ?UserId

As for how to create the foreign key, I rarely use MySQL so I don't actually know but I'm quite sure that it would be easy to find out by searching the web. For instance, if you're managing your database using MySQL Workbench:

https://www.google.com.au/search?q=...&oe=utf-8&gws_rd=cr&ei=eHyWVKiSIdfn8AXx0YCgBg

 

[AsthreA]

thank you very much for replying and giving some tips..looks like I really made a mistake there..i'm trying to make a account where each account should have their own watchlist table

 

[jmcilhinney]

thank you very much for replying and giving some tips..looks like I really made a mistake there..i'm trying to make a account where each account should have their own watchlist table

That is indeed the wrong approach. That would require you to add a new table each time you added a new user. That should never happen. Just as in C# you define a class and then create multiple instances of that class for each object, so you define a table in your database that defines an entity and then each instance of that entity is represented by a row in that table. You should almost never have two tables with the same schema, with the only legitimate reason to do so that comes to mind being where one is an archive of the other.

 

[AsthreA]

I have a new problem...I was able to do and run perfectly in mysql with the help of your example on inner join the result was this one

"SELECT TitleAnime FROM WatchList INNER JOIN animelist ON WatchList.ControlNum = animelist.ControlNum WHERE WatchList.idnum = idnum"

but when i use this query on the c# it gives me an error telling me that no database selected my database name was maindatabase but i have no idea where would i insert the database name in the query?

here my c# sample codes

void loadDB()
        {
            if (LogInScreen.welcomeuser != null)
            {

                string query = "SELECT animelist.TitleAnime FROM WatchList INNER JOIN animelist ON WatchList.ControlNum = animelist.ControlNum WHERE WatchList.idnum = idnum";


                using (MySqlConnection conn = new MySqlConnection(myConnection))
                {
                    conn.Open();

                    using (MySqlCommand cmd = new MySqlCommand(query, conn))
                    {
                        //cmd.Parameters.AddWithValue("?para", val);
                        try
                        {
                            sda = new MySqlDataAdapter();
                            sda.SelectCommand = cmd;
                            datset = new DataTable();
                            sda.Fill(datset);
                            bsource = new BindingSource();




                            bsource.DataSource = datset;
                            dataGridView1.DataSource = bsource;
                            DataGridViewColumn column = dataGridView1.Columns[0];
                            column.HeaderText = "Watchlist";
                            column.Width = 270;
                            sda.Update(datset);
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("" + ex);
                        }
                    }
                    conn.Close();
                }
            }
        }

 

[AsthreA]

seems like i was able to solve it on my own

string query = "SELECT TitleAnime FROM maindatabase.watchlist INNER JOIN maindatabase.animelist ON WatchList.ControlNum = animelist.ControlNum WHERE WatchList.idnum = idnum";

 

[wim sturkenboom]

If you have multiple users, each with their own watch list, you need a many-to-many relationship between the user and the movie table.

User table                Watchlist table           Movie table
+---------------+         +---------------+         +---------------+
| PKuserID      |-------->| FKuserID      |     +---| PKmovieID     |
+---------------+         +---------------+     |   +---------------+
| Username      |         | FKmovieID     |<----+   | Moviename     |
+---------------+         +---------------+         +---------------+

user table
1 wim
2 piet
3 klaas

movie table
1 one flew over the cuckoo's nest
2 the wall
3 some like it hot

watchlist table
1 1
1 2
2 3
3 1
3 3

select Username, Moviename
from users
join watchlist on PKuserID = FKuserID
join movies on PKmovieID = FKmovieID
where Username = 'wim'

This will give all movies on the watchlist for user 'wim'. You can change the where clause to where Moviename = 'some like it hot' and you will get all users that have that movie on their watch list.

 

[AsthreA]

great another perfect example I salute you man,with that code you provided I can use that when the time comes that I will need to filter the movies of all users that have that movie on their watch list..great thanks again for another tip