SQL Query to select from multiple nested subqueries

Question

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.

@Miggy there is the problem of a substantial Cartesian product in the existing query to consider... a times b times c times d times changelog rows — Paul Maxwell
– Paul Maxwell, Commented Nov 16, 2017 at 2:25

Paul Maxwell · Accepted Answer · 2018-08-29 07:26:40Z

1

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.

edited Aug 29, 2018 at 7:26

answered Nov 16, 2017 at 2:21

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sabarish Ramachandran · Accepted Answer · 2017-11-16 02:54:20Z

0

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

answered Nov 16, 2017 at 2:54

Sabarish Ramachandran

4314 silver badges2 bronze badges

Collectives™ on Stack Overflow

SQL Query to select from multiple nested subqueries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related