How to join tables and fetch values from a MySQL database?

To join tables, use the JOIN concept in MySQL. At first, let us create two tables.

Let us create the first table −

mysql> CREATE TABLE `demo52` (
−> `id` INT NOT NULL,
−> `name` VARCHAR(20) NOT NULL,
−> PRIMARY KEY (`id`)
−> );
Query OK, 0 rows affected (1.19 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo52 values(1,'John');
Query OK, 1 row affected (0.17 sec)

mysql> insert into demo52 values(2,'David');
Query OK, 1 row affected (0.10 sec)

mysql> insert into demo52 values(3,'Mike');
Query OK, 1 row affected (0.13 sec)

Display records from the table using select statement −

mysql> select *from demo52;

This will produce the following output −

+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | David |
|  3 | Mike  |
+----+-------+
3 rows in set (0.00 sec)

Following is the query to create second table.

mysql> CREATE TABLE `demo53` (
−> `id` INT NOT NULL,
−> `age` INT NOT NULL,
−> PRIMARY KEY (`id`),
−> CONSTRAINT `id_demo` FOREIGN KEY (`id`) REFERENCES `demo52` (`id`)
−> );
Query OK, 0 rows affected (0.57 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo53 values(1,23);
Query OK, 1 row affected (0.08 sec)

mysql> insert into demo53 values(2,22);
Query OK, 1 row affected (0.15 sec)

mysql> insert into demo53 values(3,26);
Query OK, 1 row affected (0.13 sec)

Display records from the table using select statement −

mysql> select *from demo53;

This will produce the following output −

+----+-----+
| id | age |
+----+-----+
|  1 | 23  |
|  2 | 22  |
|  3 | 26  |
+----+-----+
3 rows in set (0.00 sec)

Following is the query to get username by using id −

mysql> SELECT name FROM demo52 t1
−> JOIN demo53 t2
−> ON t1.id=t2.id WHERE t1.id=1;

This will produce the following output −

+------+
| name |
+------+
| John |
+------+
1 row in set (0.00 sec)

AmitDiwan

Published on 19-Nov-2020 13:22:47

• Related Questions & Answers
• MySQL join two tables?
• Using Aggregate function to fetch values from different tables in SAP
• Fetch similar ID records from two tables in MySQL
• How do I select four random tables from a MySQL database having thousands of tables?
• How to remove duplicate values from a MySQL table using LEFT JOIN?
• Perform MySQL LEFT JOIN on two tables?
• How to join tables in SAP system
• How to count the number of tables in a MySQL database?
• Copy from one column to another (different tables same database) in MySQL?
• Fetch records from comma separated values using MySQL IN()?
• How to get the size of the tables of a MySQL database?
• How to count horizontal values on a MySQL database?
• How can we analyze the tables of a particular database from MySQL Server command line?
• Executing an inner join over RFC on database tables in SAP system
• Compare two tables and return missing ids using MySQL LEFT OUTER JOIN