EN
MySQL - Select from multiple tables (using JOIN)
0 points
In this article, we would like to show you how to select from multiple tables using JOIN clause in MySQL.
Quick solution:
xxxxxxxxxx1
SELECT `table1`.`column1`,`table2`.`column2`2
FROM `table1`3
JOIN `table2` ON `table1`.`column_name` = `table2`.`reference_column_name`;To show how to count rows per day, we will use the following tables:
groups table:
users table:
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will display the following columns from users table:
id,username,group_id,
and the following column from groups table:
group_name
We will use aliases:
u- foruserstable,g- forgroupstable.
In our example group_id is a reference column for `groups`.`id` (g.`id`).
Query:
xxxxxxxxxx1
SELECT u.`id`, u.`username`, u.`group_id`, g.`group_name`2
FROM `users` AS u3
JOIN `groups` AS g ON u.`group_id` = g.`id`;Result:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `groups` (2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,3
`group_name` VARCHAR(50) NOT NULL,4
PRIMARY KEY (`id`)5
);6
7
CREATE TABLE `users` (8
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,9
`username` VARCHAR(50) NOT NULL,10
`group_id` INT(10) UNSIGNED,11
PRIMARY KEY (`id`),12
FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`)13
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `groups`2
(`group_name`)3
VALUES4
('Group_One'),5
('Group_Two'),6
('Group_Three');7
8
INSERT INTO `users`9
(`username`, `group_id`)10
VALUES11
('Tom', 1),12
('Kate', 2),13
('Ann', 3),14
('Jack', 2),15
('Kim', 1);