Many-to-many joins

from the Artful Common Queries page


To model a many:many relationship between two tables a and b, you need a bridging table where each row represents one instance of an association between a row in a and a row in b, as in this example:

drop table if exists users,actions,useractions;
create table users(userid int primary key, username char(32));
insert into users values(1, 'James'),(2, 'Alex'),(3, 'Justin');
create table actions(actionid int primary key, action char(32));
insert into actions values(1, 'Login'),(2, 'Logout'),(3, 'Delete'),(4, 'Promote');
create table useractions(uaid int primary key, userid int, actionid int);
insert into useractions values(1,1,1),(2,1,2),(3,3,4);

select u.username, a.action
from useractions ua
join users   u using (userid)
join actions a using (actionid);
+----------+---------+
| username | action  |
+----------+---------+
| James    | Login   |
| James    | Logout  |
| Justin   | Promote |
+----------+---------+

Almost every non-trivial database will have at least one instance of this pattern. We have a collection of articles and users' scores of them. How to report statistics on these scores? We need three tables--one for articles, one for users, and a bridge table where each row represents one score on one article by one user:

DROP TABLE IF EXISTS art_articles;
CREATE TABLE art_articles (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  title CHAR(30),
  txt TEXT,
  UNIQUE KEY (title)
);
INSERT INTO art_articles VALUES (1,'abc',''),(2,'def',''),(3,'ghi',''),(4,'jkl','');

DROP TABLE IF EXISTS art_users;
CREATE TABLE art_users(
  ID INT AUTO_INCREMENT PRIMARY KEY,
  name CHAR(20)
);
INSERT INTO art_users VALUES (1,'A'),(2,'B');

DROP TABLE IF EXISTS art_scores;
CREATE TABLE art_scores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  articleID INT NOT NULL, -- references article.articleID
  userID INT NOT NULL,    -- references user.userID
  score DECIMAL(6,2)
);
INSERT INTO art_scores VALUES (1,1,1,80),(2,1,2,90),(3,2,2,60);

-- find average score for article titled 'abc'
SELECT a.title, AVG( s.score ) AS Art1Avg
FROM art_articles a
JOIN art_scores s ON a.id=s.articleID
WHERE a.title='abc'
GROUP BY a.title;

-- find average score submitted by user 1
SELECT u.name, AVG( s.score ) AS User1Avg
FROM art_users u
JOIN art_scores s ON u.id=s.userID
WHERE u.id = 1
GROUP BY u.name;


Return to the Artful Common Queries page