CODEWITHSUNDEEP
mysqlsql
Yang
Yang

Reputation: 8701

Join with junction table, how to?

There's a schema, that looks so:

CREATE TABLE `sys_users` (

  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `last_name` varchar(255) NOT NULL,
  `available` varchar(1) NOT NULL

) DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `sys_groups_list` (

  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL

) DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `sys_groups_junction` (

 `group_id` INT NOT NULL, /* <-- Refers to sys_groups_list.id */
 `usr_id` INT NOT NULL /* <-- Refers to sys_users.id */

) DEFAULT CHARSET=UTF8;

On input, I have an id of some user. On output, the result must look like so:

id  | last_name | available | group_name
... | ........  | ......... | ..........

I'm not sure how to write 3 JOINs correctly to accomplish this, anytime I try I end up with syntax errors. So can you suggest how to write the SQL correctly?

Upvotes: 1

Views: 68

Answers (1)

John Hodge
John Hodge

Reputation: 1715

SELECT sys_users.id, lastname, available, sys_groups_list.name
FROM sys_users
JOIN sys_groups_junction
ON sys_users.id = sys_groups_junction.usr_id
JOIN sys_groups_list
ON sys_groups_junction.group_id = sys_groups_list.id

Upvotes: 2

Related Questions