CODEWITHSUNDEEP
mysqlsqldatabase
tb95
tb95

Reputation: 15

SQL Query to select from multiple nested subqueries

I am having a bit of trouble with this SQL query and I can't wrap my head around it. I have a site where users which have a moderator-like role can make changes to the user's account type below them, now I do have a working changelog, but I have problems displaying the correct information. I have these three tables:

Roles: Role_ID, Role_Type

Users: User_ID, Firstname, Lastname, Role_ID

Changelogs: Log_ID, Moderator_ID, User_ID, Prev_Role_ID, New_Role_ID, Logtime

To explain, the changelogs have a unique Log_ID, and then the Moderator_ID is the User_ID of the person making changes, User_ID is then the ID of user's account that hs been changed. And the Role_ID's are just what it was to what is has changed to.

So I want to display the Log ID, the first name and last name of the moderator, the first name and last name of the user, and the previous role as well as new role.

I am using nested selects, but I think my problem is with the joining of tables, because everything displays fine, except I have a LOT of records, and when I group by Log_ID at then end, it just ends up showing my records but with the incorrect changes to roles. It returns all the combinations, so I think I need a few JOINS but I am not sure what or where to put them.

SELECT changelogs.log_id, a.lastname AS mlastname, a.firstname AS mfirstname, 
b.lastname AS ulastname, b.firstname AS ufirstname, c.role_type AS 
pre_role, d.role_type AS new_role, changelogs.logtime 

FROM (SELECT log_id, lastname, firstname FROM users, changelogs 
      WHERE users.user_id = changelogs.mod_id) a,

     (SELECT log_id, lastname, firstname FROM users, changelogs 
      WHERE users.user_id = changelogs.user_id) b,

     (SELECT log_id, role_type FROM roles, changelogs 
      WHERE changelogs.prev_role_id = roles.role_id) c,

     (SELECT log_id, role_type FROM roles, changelogs 
      WHERE roles.role_id = changelogs.role_id) d, changelogs;

I basically just want to view my Changelogs table exactly as it is, just with the names in the place of their ID's and the roles in the place of their ID's.

RESULT: Log_ID, mLastname, mFirstname, uLastname, uFirstname, Pre_Role, New_Role, Time

Sorry for this silly question but it has been bugging me for quite a while.

Upvotes: 1

Views: 127

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35603

Give each joined table a meaningful alias (don't use a sequence a,b,c,d...) and you don't need subqueries here:

SELECT
      c.log_id
    , m.lastname  AS mlastname
    , m.firstname AS mfirstname
    , u.lastname  AS ulastname
    , u.firstname AS ufirstname
    , p.role_type AS pre_role
    , r.role_type AS new_role
    , c.logtime
FROM changelogs c
INNER JOIN users u ON c.user_id = u.user_id
INNER JOIN users m ON c.mod_id = m.user_id 
INNER JOIN roles r ON c.role_id = r.role_id 
INNER JOIN roles p ON c.prev_role_id = p.role_id

tip any time you find yourself typing a comma after a table name, STOP. Remove the comma and think about you need to join this table. You will be using good explicit joins in no time.

Upvotes: 1

Sabarish Ramachandran
Sabarish Ramachandran

Reputation: 421

Try this solution.. It should work.

SELECT a.log_id, c.lastname  AS mlastname, c.firstname AS mfirstname, b.lastname  AS ulastname
    , b.firstname AS ufirstname, e.role_type AS pre_role, d.role_type AS new_role, a.logtime
FROM Changelogs a
JOIN Users b ON a.user_id = b.user_id
JOIN Users c ON a.moderator_id = c.user_id 
JOIN Role d ON a.New_Role_id = d.Role_ID 
JOIN Role e ON a.prev_role_id = e.Role_ID

Upvotes: 0

Related Questions