Mysql – SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once

foreign keyforeign-dataMySQL

Table A (users)

id     | name   | ip
52433    Yashas   1.1.1.1
512      Admin    5.6.7.8

Table B (admin_record)

id    | admin | time    | date    | reason | action 
52433   512     2312352   1213213   Test     WARN

id is FOREIGN key

Table B keeps a log of all actions an administrator takes on his users. And the table A stores the account information of the users & administrators.

I am trying to display a list of actions taken by a administrators on a user.

I want to write a query which gets me all the rows of table B where id = fixed number.

Here is what I want to display:

"Administrator1 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.
"Administrator2 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.
"Administrator3 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.

All I have is the user's id using which I need to obtain all the entries in the log along with the administrator names. I need to obtain the admin's name, users name and all the contents of table B related to a particular user in one single query. If possible, I want the to change the field name(not literally change, but want the SQL server to send the data with new field names to separate admin name & user name) of the admin's name to admin_name and user's name as . I mean something like this

SELECT users.name AS admin_name WHERE users.id = %i 
AND SELECT users.name AS user_name WHERE users.id = %i

(the query isn't correct,it us just an example to show what I am trying)

Progress so far:

 SELECT player.name, player.ip, admin.name, record.date, record.time, record.action, record.reason
FROM admin_record AS record
JOIN users AS player ON player.id = record.id
JOIN users AS admin ON admin.id = record.admin
WHERE player.id=%i

where admin_record is table B & users is table A.

Best Answer

The question is all over the place but I think this is what you are looking for 

select a.name, u.name, b.note, b.date     
  from table_B as B 
  join table_A as u 
        on u.ID = B.ID 
  join table_A as a 
        on a.ID = B.admin_id  
 where B.ID = @userID

you can create a line with
select a.name + " took action on " + u.name ...

Related Question