MySQL query table join ?

Anon

I have 2 tables involved in a query that are as follows:

bids
bid_id
client_id
project_topic
user_id
modified_by

client_users
user_id
client_id
first_name
last_name

What I want is to have a user be able to view all bids that are associated with their company (in other words, all bids that have the same client_id as they do, which is stored in a session when the user logs in). I need to then list the bids, display the first_name and last_name of the person who submitted the bid (user_id in the bids table), and the first_name and last_name of the person who modified the bid (modified_by in the bids table). (The two are not necessarily going to be the same.)

So the result I am looking for would look something like this:

| Bid Id | Submitted By | Modified By |

| 3 | Joe Smith | Mary Jones |

| 7 | Bob Lewis | Joe Smith |

Both the submitted_by and modified_by are stored as the user_id (relevant to the client_users table). Thus far I have:

$sql = "SELECT B.bid_id, B.project_topic, B.modified_by, C.first_name, C.last_name
FROM bids B, client_users C
WHERE B.client_id = '".$HTTP_SESSION_VARS['ccl']['client_id']."'
AND C.user_id = B.user_id";

This pulls all of the correct data, but shows the user_id for the Modified By column. What would be the correct query for also getting the corresponding first_name and last_name for the modified_by field? Much thanks in advance!

Anon

You need two instances of the client_user table.

$sql = "SELECT B.bid_id, B.project_topic, B.modified_by, C1.first_name as submitterFirstName c1.last_name as submitterFirstName, C2.first_name as modifierFirstName c2.last_name as modifierFirstName
FROM bids B, client_users C1 , client_users C2
WHERE B.client_id = '".$HTTP_SESSION_VARS['ccl']['client_id']."'
AND C1.user_id = B.user_id and B.modified_by = c2.user_id";

Anon

Thanks for the fast reply! Creating two instances of the client_user table worked great. Now the problem is that if the bid has not been modified, and the modified_by field is empty, then it won't return that result. Is there a way to fix that in the query? Thanks for the help.

Anon

Yes, an outer join ought to do this

$sql = "SELECT B.bid_id, B.project_topic, B.modified_by, " .
"C1.first_name as submitterFirstName c1.last_name as submitterFirstName, " .
"C2.first_name as modifierFirstName c2.last_name as modifierFirstName " .
"client_users C1 inner join bids B on C1.user_id = B.user_id " .
"left outer join client_users C2 on B.modified_by = c2.user_id " .
" WHERE B.client_id = '" . $HTTP_SESSION_VARS['ccl']['client_id'] . "'";

if the modified_by column is null, then c2.first_name and c2.last_name will show up as null in the result.