Help with sub-query, possibly 2?

[coupe-r]

Hi All,

 

I have a page where you can create or edit user data.  When you click the edit button, a box displays and tells you who and when that user was created AND who and when the user was updated last.

 

The issue is that the 'createdBy' and 'updatedBy' names are not correct, because of my query.  I'm guess this is because all this data is within the same table and I'm not joining any tables?

 

Also, the created_by and updated_by fields are user_id's of the user who created or updated it.

 

Thanks for the help in advance.

 

 

SELECT firstname, lastname, email, level, deactivated_on, updated_on, created_on,

(SELECT CONCAT( firstname,' ', lastname)
FROM users 
WHERE user_id = created_by AND deleted_on IS NULL) As createdBy,

        (SELECT CONCAT( firstname,' ', lastname)
FROM users 
WHERE user_id = updated_by AND deleted_on IS NULL) As UpdatedBy

FROM users
WHERE user_id = '".$page_user_id."' AND deleted_on IS NULL AND client_id = '".$_SESSION['client_id']."'

 

 

-- USERS Table --
user_id              	INTEGER
client_id             	INTEGER
firstname           	VARCHAR(25)
lastname            VARCHAR(30)
email                	VARCHAR(55)
password         	VARCHAR(40)
level                   TINYINT
deactivated_on	     DATETIME*
created_on       	DATETIME
created_by       	INTEGER
updated_on           	DATETIME*
updated_by      	INTEGER*
deleted_on      	DATETIME*
deleted_by        	INTEGER*

[coupe-r]

I got it working with 3 different queries, but if someone could still help with a single query, that would be great.

 

Here are the 3 queries that work right now:

 

SELECT firstname, lastname, email, level, deactivated_on, updated_on, created_on, created_by, updated_by
FROM users
WHERE user_id = '".$page_user_id."' AND deleted_on IS NULL AND client_id = '".$_SESSION['client_id']."'


SELECT CONCAT(firstname,' ', lastname) AS createdBy
FROM users
WHERE user_id = '".$row['created_by']."' AND deleted_on IS NULL AND client_id = '".$_SESSION['client_id']."'


SELECT CONCAT(firstname,' ', lastname) AS UpdatedBy
FROM users
WHERE user_id = '".$row['updated_by']."' AND deleted_on IS NULL AND client_id = '".$_SESSION['client_id']."'

[Mahngiel]

You can join a table to itself.  I'm no SQL guru and i don't have a fake table to test this on, but it seems logical.  Try this in phpMyAdmin (or equivalent tool) first.

 

<?php

SELECT CONCAT(`firstname`, `lastname`) as 'creator', `email`, `level`, `deactivated_on`, `updated_on`, `created_on`, `created_by`, `updated_by`
FROM `users`
INNER JOIN `users` on `users`.`created_by` = `users`.`user_id`
INNER JOIN `users` on `users`.`updated_by` = `users`.`user_id`
WHERE `user_id` = 3;

[Mahngiel]

Alright, i was almost there on my first post, but I had to try it out and get something that works, and I'll explain it after i show it.

 

<?php
SELECT 
    concat( `b`.`firstname` , ' ', `b`.`lastname` ) AS `creator` , 
    concat( `c`.`firstname` , ' ', `c`.`lastname` ) AS `updater` , 
    `a`.`user_id` , 
    concat( `a`.`firstname` , ' ', `a`.`lastname` ) AS 'user'
FROM `test` `a`
RIGHT JOIN `test` `b` ON `b`.`user_id` = `a`.`createdby`
RIGHT JOIN `test` `c` ON `c`.`user_id` = `a`.`updatedby`
WHERE `a`.`user_id` =1
LIMIT 1 

Consider a simple table that looks like this:

 

Looking firstly at the FROM and two INNER JOINS:  MySQL doesn't give a rat's ass if you're joining two different tables, or a table on itself.  However, if you're going to join a table onto itself, you need to specify an alias for the table name so it can differentiate what you're doing.  FROM `test` `a` is referencing the base table (and row in this case) that we want to seek other data for and assigning it the alias a.  The two following joins are using the same table and an incrementing alias for it. 

 

After the alias is established for the table's name in the join, you specify where to make the join.  In this scenario, you want to grab the user_id for a different user that matches the originating row's created_by.  Doing the above with a SELECT * will provide something like this:

 

Back to the selection, you'll once again need the aliases to specify which rows you'll want to obtain.  All you want is the concatenated first & last name so that makes it simple and you can supply another alias for them.  In the end, my example will yield this in one query versus 3:

[coupe-r]

Wow.  I really appreciate the time it took you to work this out for me...  I will try this when I get home from work today.  Thanks again!!