SQL Tutorial – MySQL Many-to-Many Relationship

sql-tutorial-mysql-script-many-to-many-mapping-feature-image

In the tutorial, we guides how to use SQL queries to create Many-to-Many association tables with MySQL database.

Goal

Many-to-Many relationship:

Create 3 tables:

user table -> attributes: (id, email, username, password)
role table -> attributes: (id, description, name)
user_roles table -> attributes: (user_id, role_id)

Create Tables

SQL creating table syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

– Create User table ->

CREATE TABLE user (
	id bigint(20) NOT NULL, 
	email varchar(40) NOT NULL,
	username varchar(15) NOT NULL,
	password varchar(100) NOT NULL,
	PRIMARY KEY (id)
);

sql-many-to-many-tables-create-user-table

– Create Role table ->

CREATE TABLE role (
	id bigint(20) NOT NULL,
	name varchar(60) NOT NULL, 
	PRIMARY KEY (id)
);

sql-many-to-many-tables-create-role-table

– Create User_Roles table ->

CREATE TABLE user_roles (
	user_id bigint(20) NOT NULL,
	role_id bigint(20) NOT NULL,
	FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE CASCADE,
	FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE RESTRICT ON UPDATE CASCADE,
	PRIMARY KEY (user_id, role_id)
);

sql-many-to-many-tables-create-user-role-table

Insert Data Into Tables

Insert Into syntax:

  • Specifies both the column names and the values to be inserted
    INSERT INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...);
    
  • If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query.
    INSERT INTO table_name
    VALUES (value1, value2, value3, ...);
    

– Insert into user table:

INSERT INTO user VALUES (1, 'jack@ozenero.com', 'Jack', 'jack123');
INSERT INTO user VALUES (2, 'adam@ozenero.com', 'Adam', 'adam123');

sql-many-to-many-tables-insert-data-to-user-table

– Insert into role table:

INSERT INTO role VALUES (3, 'ADMIN');
INSERT INTO role VALUES (4, 'USER');

sql-many-to-many-tables-insert-data-to-role-table

– Insert into user_roles table:

INSERT INTO user_roles VALUES (1, 3);
INSERT INTO user_roles VALUES (1, 4);
INSERT INTO user_roles VALUES (2, 4);

sql-many-to-many-tables-insert-data-to-user_roles-table

Join Query

SELECT user.id, user.email, user.username, role.id AS role_id, role.name AS role_name
FROM user 
JOIN user_roles on (user.id=user_roles.user_id)
JOIN role on (role.id=user_roles.role_id);

-> Result:

sql-many-to-many-tables-join-query

69 thoughts on “SQL Tutorial – MySQL Many-to-Many Relationship”

  1. After I originally commented I clicked the -Notify me when new feedback are added- checkbox and now each time a comment is added I get four emails with the same comment. Is there any way you may remove me from that service? Thanks!

  2. Howdy! I could have sworn I’ve been to this site before but after reading through some of the post I realized it’s new to me. Nonetheless, I’m definitely delighted I found it and I’ll be bookmarking and checking back often!

  3. I’m really enjoying the design and layout of your blog.
    It’s a very easy on the eyes which makes it much more enjoyable
    for me to come here and visit more often. Did you
    hire out a designer to create your theme? Fantastic
    work!

  4. I should say also believe that mesothelioma is a rare form of cancers that is typically found in those previously subjected to asbestos. Cancerous cells form from the mesothelium, which is a protective lining that covers many of the body’s bodily organs. These cells ordinarily form in the lining with the lungs, tummy, or the sac that encircles one’s heart. Thanks for expressing your ideas.

  5. Hi there, just became alert to your blog through Google, and found that it is really informative. I抦 gonna watch out for brussels. I will appreciate if you continue this in future. Lots of people will be benefited from your writing. Cheers!

  6. One thing is one of the most popular incentives for using your credit cards is a cash-back or even rebate offer. Generally, you will get 1-5 back for various expenditures. Depending on the card, you may get 1 returning on most expenditures, and 5 back on expenses made going to convenience stores, gasoline stations, grocery stores as well as ‘member merchants’.

  7. Thanks for the ideas you have contributed here. Yet another thing I would like to mention is that personal computer memory specifications generally increase along with other improvements in the technologies. For instance, whenever new generations of cpus are brought to the market, there is certainly usually a related increase in the type calls for of all computer memory and hard drive room. This is because the software program operated by means of these processors will inevitably increase in power to make use of the new technologies.

  8. Hey I know this is off topic but I was wondering if you knew of any widgets I
    could add to my blog that automatically tweet my newest twitter updates.
    I’ve been looking for a plug-in like this for quite some time and was
    hoping maybe you would have some experience with something like this.
    Please let me know if you run into anything. I truly enjoy reading your blog
    and I look forward to your new updates.

  9. Pretty section of content. I just stumbled upon your weblog
    and in accession capital to assert that I get in fact enjoyed account your blog posts.
    Any way I will be subscribing to your augment and even I achievement you access consistently
    fast.

  10. Do you have a spam problem on this website; I also am
    a blogger, and I was wanting to know your situation; many of us have created some nice
    practices and we are looking to swap solutions with other folks,
    be sure to shoot me an email if interested.

  11. Fantastic beat ! I would like to apprentice while you
    amend your web site, how could i subscribe for a blog
    website? The account aided me a applicable deal.

    I have been a little bit familiar of this your broadcast offered vibrant clear idea

  12. Excellent blog here! Also your web site loads up fast!

    What web host are you using? Can I get your affiliate link to your host?
    I wish my website loaded up as quickly as yours lol

  13. You really make it appear really easy together with your presentation however
    I to find this topic to be really one thing which I feel I
    might never understand. It sort of feels too complicated and extremely huge for me.
    I am looking ahead to your next submit, I’ll try to
    get the cling of it!

  14. Having read this I believed it was very informative.
    I appreciate you finding the time and energy to put this content
    together. I once again find myself personally spending way too much time both reading and leaving comments.
    But so what, it was still worth it!

  15. I absolutely love your blog and find almost
    all of your post’s to be just what I’m looking for. Do you
    offer guest writers to write content for you? I
    wouldn’t mind writing a post or elaborating on most of the subjects you write related to here.
    Again, awesome web log!

  16. Have you ever thought about adding a little bit more
    than just your articles? I mean, what you say is important and all.
    However just imagine if you added some great visuals or videos to give your posts
    more, “pop”! Your content is excellent but with pics and video clips,
    this site could undeniably be one of the most beneficial
    in its niche. Amazing blog!

  17. We’re a bunch of volunteers and starting a new scheme in our community.

    Your web site provided us with helpful info to work on. You’ve
    done an impressive job and our entire community will likely be thankful to you.

  18. whoah this blog is great i like studying your articles.
    Keep up the good work! You recognize, a lot of persons are hunting around for this information, you can aid them greatly.

  19. Do you mind if I quote a few of your posts as long as I provide credit and sources back to your blog?
    My blog is in the exact same niche as yours and my visitors
    would genuinely benefit from some of the information you present here.
    Please let me know if this okay with you. Thanks a lot!

  20. Wow that was unusual. I just wrote an incredibly long comment
    but after I clicked submit my comment didn’t show up.
    Grrrr… well I’m not writing all that over again. Anyway, just wanted to
    say wonderful blog!

  21. What’s Taking place i’m new to this, I stumbled upon this I’ve found It absolutely helpful and it has aided me out loads.
    I hope to give a contribution & assist different customers like its aided me.
    Good job.

  22. Woah! I’m really enjoying the template/theme of this site.

    It’s simple, yet effective. A lot of times it’s difficult
    to get that “perfect balance” between usability and appearance.
    I must say you have done a superb job with this. Also, the blog loads extremely quick for me on Safari.
    Excellent Blog!

  23. Can I just say what a comfort to discover a person that really understands what they
    are discussing on the web. You actually know how to bring a problem to light and make it important.
    More people really need to check this out and understand
    this side of the story. I was surprised you are not more popular because you most certainly
    possess the gift.

  24. Hello just wanted to give you a brief heads up and let you know a few of the images aren’t loading properly. I’m not sure why but I think its a linking issue. I’ve tried it in two different internet browsers and both show the same outcome.

  25. Hello there, You’ve done an incredible job. I’ll definitely digg it and personally recommend to my friends.
    I am sure they’ll be benefited from this website.

  26. I have to thank you for the efforts you’ve put in writing this blog.
    I am hoping to see the same high-grade blog posts from you in the future as well.
    In truth, your creative writing abilities has encouraged me to get my own website now
    😉

  27. We are a group of volunteers and opening a new
    scheme in our community. Your web site provided us
    with valuable info to work on. You’ve done a formidable job
    and our whole community will be thankful to you.

  28. This is the right webpage for everyone who hopes to find out about
    this topic. You understand so much its almost tough to argue with you (not that I personally would want
    to…HaHa). You certainly put a new spin on a subject that has been written about for
    a long time. Great stuff, just great!

  29. I believe everything said was actually very reasonable.
    However, think about this, suppose you were to write a killer headline?
    I ain’t saying your information is not solid., however what if
    you added a title that makes people desire more?
    I mean ozenero | Mobile & Web Programming Tutorials is a little vanilla.
    You ought to glance at Yahoo’s front page and
    watch how they create news headlines to get people to click.
    You might add a related video or a pic or two to grab people excited about what you’ve written. Just my opinion, it might bring your posts a little bit more interesting.

  30. My partner and I stumbled over here from a different web address and thought I might
    check things out. I like what I see so now i’m following you.
    Look forward to checking out your web page repeatedly.

  31. Wе are a group of volunteers and openkng а bгand
    neww ѕcheme in our commսnitү. Your webseite provіded us with useful information to work on. Y᧐u’ve done an imρressive prοcess and our entire community
    mіght be thankful to you.

  32. Greate post. Keep posting such kind of information on your blog.
    Im really impressed by it.
    Hi there, You’ve performed an incredible job. I’ll certainly
    digg it and in my view recommend to my friends.
    I am sure they’ll be benefited from this site.

  33. Hοwdy! Tһis is my first visit to yоur blog! We are a team of voⅼunteers and starting a new project in a
    community іn the same niche. Youur blog provided us beneficial information tto wоrk
    on. Yoս have done a extraordinary jⲟb!

  34. Woah! I’m really digging the template/theme of this site.
    It’s simple, yet effective. A lot of times it’s challenging to get that “perfect balance” between superb usability and visual appeal.
    I must say you have done a great job with this. In addition,
    the blog loads extremely fast for me on Safari. Outstanding Blog!

  35. Hello there! This is kind of off topic but I need some
    help from an established blog. Is it very hard to set up your
    own blog? I’m not very techincal but I can figure
    things out pretty quick. I’m thinking about making my own but I’m not sure where to begin. Do you
    have any ideas or suggestions? Appreciate it

  36. A motіvating disⅽussion is woreth comment. I do believe that you need to publish more on this
    subject, it miցht not be a taboo subject but typicalⅼy folks ԁon’t discuss such issuеs.
    To the next! Bestt wishes!!

  37. Hey! This is my first comment here so I just wanted to give a quick shout out and tell you I really enjoy reading through your blog
    posts. Can you suggest any other blogs/websites/forums that go over the same subjects?

    Thank you so much!

  38. Thank you for the good writeup. It in fact used
    to be a leisure account it. Glance complicated to far added agreeable from you!
    However, how could we keep in touch?

Leave a Reply

Your email address will not be published. Required fields are marked *