How to get username using ID from another table in MySQL database?

To get username using ID from two tables, you need to use JOIN and join the tables.

Let us create a table −

Example

mysql> create table demo77
   -> (
   -> userid int not null primary key,
   -> username varchar(20)
   -> );
Query OK, 0 rows affected (2.63

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

Example

mysql> insert into demo77 values(1,'John');
Query OK, 1 row affected (0.19

mysql> insert into demo77 values(2,'Bob');
Query OK, 1 row affected (0.36

Display records from the table using select statement −

Example

mysql> select *from demo77;

This will produce the following output −

Output

+--------+----------+
| userid | username |
+--------+----------+
|      1 | John     |
|      2 | Bob      |
|      3 | Mike     |
+--------+----------+

Following is the query to create second table −

Example

mysql> create table demo78
   -> (
   -> id int not null primary key,
   -> countryname varchar(20),
   -> constraint fk_id foreign key(id) references demo77(userid)
   -> );
Query OK, 0 rows affected (0.75

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

Example

mysql> insert into demo78 values(1,'US');
Query OK, 1 row affected (0.14

mysql> insert into demo78 values(2,'AUS');
Query OK, 1 row affected (0.15

Display records from the second table using select statement −

Example

mysql> select *from demo78
   -> ;

This will produce the following output −

Output

+----+-------------+
| id | countryname |
+----+-------------+
|  1 | US          |
|  2 | AUS         |
+----+-------------+
2 rows in set (0.00 sec)

Following is the query to get username by using id by joining both the tables −

Example

mysql> select username from demo77
   -> join demo78
   -> on demo77.userid=demo78.id;

This will produce the following output −

Output

+----------+
| username |
+----------+
| John     |
| Bob      |
+----------+
2 rows in set (0.05 sec)