weird join in php
Okay so I'm not a novice to PHP nor am I a novice to MySQL or joins...  But what I have to do, i'm not completely sure it's possible without running a foreach to inject the data into my current array...

So, I have a join that is like so:

    SELECT `priorities`.`pri_name`, `agent_status`.`sta_title`, `specializations`.`spec_title`, `agents`.*
    FROM (`agents`)
    JOIN `priorities` ON `priorities`.`id` = `agents`.`priority`
    JOIN `agent_status` ON `agent_status`.`s_id` = `agents`.`user_status`
    JOIN `Specializations` ON `specializations`.`id` = `agents`.`Specialization`
    WHERE `agents`.`id` =  '4';

Now, I need to add one more join to this which under normal circumstances wouldn't be an issue.  But what I need to figure out, is how in this single query can i create a join on the same table but a different record.

So basically I'd need to select is the f_name, l_name of the 2nd record where the id is the same as the pid of the initial selected record...  so something like adding into my select:

    agents.f_name AS pf_name, agents.l_name AS pl_name

then having something like...

    JOIN('agents' ON agents.id = agents.p_id

or something like that...  
weird join in php
===

Okay so I'm not a novice to PHP nor am I a novice to MySQL or joins...  But what I have to do, i'm not completely sure it's possible without running a foreach to inject the data into my current array...

So, I have a join that is like so:

    SELECT `priorities`.`pri_name`, `agent_status`.`sta_title`, `specializations`.`spec_title`, `agents`.*
    FROM (`agents`)
    JOIN `priorities` ON `priorities`.`id` = `agents`.`priority`
    JOIN `agent_status` ON `agent_status`.`s_id` = `agents`.`user_status`
    JOIN `Specializations` ON `specializations`.`id` = `agents`.`Specialization`
    WHERE `agents`.`id` =  '4';

Now, I need to add one more join to this which under normal circumstances wouldn't be an issue.  But what I need to figure out, is how in this single query can i create a join on the same table but a different record.

So basically I'd need to select is the f_name, l_name of the 2nd record where the id is the same as the pid of the initial selected record...  so something like adding into my select:

    agents.f_name AS pf_name, agents.l_name AS pl_name

then having something like...

    JOIN('agents' ON agents.id = agents.p_id

or something like that...  