get rows from another mysql table depening on distinct value from another mysql table-CodePudding

I have two mysql tables.

UserInformation

and

referencelog

Now, most of the columns are not of interest. This is what I am trying to do:

Get the unique "created_by_user_name" from referencelog for a given level id. I am able to do it by following query, which returns 3 unique user_name.

SELECT DISTINCT log.created_by_user_name FROM ReferenceScreenResultLog log WHERE level="1"

but I want to some how join this with UserInformation table, so I get the rows from UserInformation. As in I want the three rows from UserInformation table corresponding to these 3 unique user names I get from above query. The columne user_name in UserInformation table is connected with created_by_user_name column in referencelog table.

CodePudding user response：

assuming that the two table are joined by usr.id = log.created_by
the you could use a join this way

SELECT DISTINCT usr.first_name, log.created_by_user_name 
FROM ReferenceScreenResultLog log 
INNER JOIN UserInformation usr on usr.id = log.created_by 
WHERE level="1"

or for all usr row content

SELECT DISTINCT usr.*, log.created_by_user_name 
FROM ReferenceScreenResultLog log 
INNER JOIN UserInformation usr on usr.id = log.created_by 
WHERE level="1"