-3

I have a table called 'auditlog' which contains the columns (id, user, action project, info, time, project_ref, workpackage_ref, partner_ref)

and i have another table called 'role' which contains the following columns (id, role, description)

i want to select the whole 'auditlog' table in addition to that i want the 'role' column from 'role' table to be the 3rd column in my selection, and the rest won't be changed

Ahmad Naser AlMuhtar
  • 1
  • 2

2 Answers2

0
SELECT
a.id,
a.user,
r.role,
a.action,
a.project,
a.info,
a.time...
FROM auditlog a
LEFT JOIN role r ON r.id = a.id
Samuel Wahlberg
  • 145
  • 7
  • it works but there is a problem, that one user can have more then one role and more than once action, how can i collecting them in one row instead of showing me for example 10 rows for the same user but everytime we have a different role and a different action @samuel Wahlberg – Ahmad Naser AlMuhtar Jan 15 '21 at 12:19
  • What is the connection between the 'auditlog' and 'roles' tables? I assumed the id was the userid but not I think it's not. I think you should look a bit more into db design. Look at 'one to many' and 'many to many' relationships. Check the first respons in this question: https://stackoverflow.com/questions/3113885/difference-between-one-to-many-many-to-one-and-many-to-many – Samuel Wahlberg Jan 15 '21 at 12:37
0

You can use JOIN in sql.

SELECT a.*,r.role FROM auditlog AS a LEFT JOIN role AS r ON a.id=r.id

You should edit this as your logic.

oraterlee
  • 1
  • 1