Inner Join Problem

sfmnetsys

I am trying to pull data out of a column named realname from a table named user based on a userid that is associated with the realname in the user table and in a table named calls.

I keep getting the following error:
Column: 'userid' in where clause is ambiguous

I have tried several select statements in the MySQL client, but I can't seem to figure out the correct code.

Thanks.

$display_realname_sql = "SELECT realname FROM user INNER JOIN calls ON calls.userid = user.userid WHERE userid = '$userid'
";

Anon

One way to write it could be this depending on what database you are using:

SELECT u.realname
FROM user u, calls c
WHERE u.userid = c.userid
AND u.userid = '. $user_id .';

.. but I am not even sure why you need to access "calls" if realname and userid are both in the same table, you could just do this...

SELECT realname
FROM user
WHERE userid = '.$user_id .';

... you only need to do an INNER JOIN if you are looking for information that is in two separate tables.

sfmnetsys

Okay. Let's say I don't need a join statement. My question then becomes how can I take the userid in a table named calls and translate the userid into a realname listed in user. The current output, in a list format, is the CallID, CallTypeID (listed as an integer), CallDateTime, CallSubject, userid (listed as an integer), and CallStatus. Multiple calls will be listed so I will need to translate multiple userids.

Drew Adams wrote:

One way to write it could be this depending on what database you are using:

SELECT u.realname
FROM user u, calls c
WHERE u.userid = c.userid
AND u.userid = '. $user_id .';

.. but I am not even sure why you need to access "calls" if realname and userid are both in the same table, you could just do this...

SELECT realname
FROM user
WHERE userid = '.$user_id .';

... you only need to do an INNER JOIN if you are looking for information that is in two separate tables.

[deleted]

Column: 'userid' in where clause is ambiguous.

That's because that column appears in both tables, and you didn't tell SQL which of the two you mean. Just prepend the tablename:

SELECT realname
FROM user
INNER JOIN calls
ON calls.userid = user.userid
WHERE calls.userid = '$userid'

Anon

"Okay. Let's say I don't need a join statement. My question then becomes how can I take the userid in a table named calls and translate the userid into a realname listed in user."

The only way to do this is if there is a userid field in user that matches the userid in Calls. Is there a Userid field in User?

sfmnetsys

Yes there is.

Anon

Well, if I am understanding correctly, I would construct a SQL statement that looks like this.

SELECT u.realname, c.CallID, c.CallTypeID, c.CallDateTime, c.CallSubject
FROM user u, calls c
WHERE u.userid = c.userid
AND u.userid = '. $user_id .';