Ragith Thomas Ragith Thomas - 24 days ago 10
SQL Question

sql query to get only one name if id exists in both table with different records

Thanks in advance, I have two tables one is users table and another one is checks table. users table contain all users list and checks table contains all the data checked by the users so it contain rows with different adminids. So how to write a sql query to get the user name if the checks table contain atleast one adminid.

My users table:

id |username |Phone |email |role
1 | Girish |9834343468|girish@yahoo.com |admin
2 | Rakesh |9434343432|rakesh@gmail.com |admin
3 | Suresh |9434343772|suresh@gmail.com |developer


checks table:

id | userid | username | admin_id |order_id |exam |
1 | 3 | suresh | 1 | df54545454fg09|
2 | 3 | suresh | 1 | df54545454fg09|
3 | 3 | suresh | 2 | gh54995454fg55|


I need a sql query where atleast one "admin_id" of checks table contain users table "id"

I want to create a table like this

id|admin_id|name |email |Phone
1 | 1 |Girish |girish@gmail.com |9834343468
2 | 2 |Rakesh |rakesh@gmail.com |9434343432

Answer

Try below query

SELECT u.id,a.admin_id,u.username name, u.email, u.Phone
FROM users u,
(SELECT DISTINCT admin_id
FROM checks c) a
WHERE u.id=a.admin_id

This may help you.