1

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.

1

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 ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.