Help with a LEFT JOIN pulling different users

[coupe-r]

Hi all,

 

I have a prop_notes table with created_by and updated_by.  Each are set to a users ID.

 

Here is the query:

 

$sql  = "SELECT pr.note, pr.updated_on, pr.updated_by, pr.created_on, pr.created_by, u.firstname, u.lastname FROM prop_notes pr ";
$sql .= "LEFT JOIN users u ON pr.created_by = u.user_id ";
$sql .= "WHERE pr.client_id = '".$_SESSION['client_id']."' AND pr.note_id = '".$page_note_id."' AND pr.deleteStatus = '0'";

 

I am joining on the created_by ID, which only gives me the created_by (firstname, lastname).  I need to be able to grab the firstname/lastname of both the created_by and the updated_by data.

 

Any ideas on how to make this query work?

[gristoi]

You don't need to use a left join, your are trying to return data from both tables so remove the LEFT. Also, you could concatenate the usernames to make the query a bit easier to read:

CONCAT(u.firstname,' ', u.lastname) As UserName

[coupe-r]

Hey, thanks man.

 

But the prop_notes table has the user_id in the created_by / updated_by fields.  I'm trying to join with the Users table to get the first and last names of each of those users.

 

Unfortunately, removing the LEFT doesn't work.

 

Thanks again.

[gristoi]

Hi, sorry i misread your problem, was not firing on all cylinders (too early) lol.

 

You might want to try using a nested select query. But be aware that if you are using a nested query the nested select can only return 1 row. Have a look at the query below and see if this puts you on the right tracks

 

SELECT pr.note, pr.updated_on, pr.created_on, CONCAT( u.firstname,' ', u.lastname) AS UpdatedBy,
(SELECT CONCAT( u.firstname,' ', u.lastname)  FROM prop_notes pr  JOIN users u ON pr.created_by = u.user_id   ) As createdBy
FROM prop_notes pr 
JOIN users u ON pr.created_by = u.user_id 
WHERE pr.client_id = '".$_SESSION['client_id']."' AND pr.note_id = '".$page_note_id."' AND pr.deleteStatus = '0'

[coupe-r]

Thanks again.  The SQL you had gave me multiple subquery results, so I added another WHERE clause in the sub query and it works.  Let me know if this is correct.

 

SELECT pr.note, pr.updated_on, pr.created_on, CONCAT( u.firstname,' ', u.lastname) AS UpdatedBy,
(SELECT CONCAT( u.firstname,' ', u.lastname)  FROM prop_notes pr  JOIN users u ON pr.created_by = u.user_id
WHERE pr.client_id = '1' AND pr.note_id = '28' AND pr.deleteStatus = '0') As createdBy
FROM prop_notes pr
JOIN users u ON pr.created_by = u.user_id
WHERE pr.client_id = '1' AND pr.note_id = '28' AND pr.deleteStatus = '0'

[gristoi]

Looks fine, it was only to give you a starting point and hopefully point you in the right direction.

[coupe-r]

got it!!

 

Thank you for your time..