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)