[RESOLVED] Selecting from 3 tables with Left Join - Please help

lpa

The query below returns error "Unknown column 'profileviewers.visitor_id' in 'on clause'", but this column exists. Can somebody help me?

Thank you.

$result = mysql_query("SELECT 
profileviewers.visitor_id, 
profileviewers.visittime,

usertable.nickname, 
usertable.sex, 
usertable.photo, 

session.user

FROM profileviewers, usertable 
LEFT JOIN session
ON profileviewers.visitor_id=session.user
WHERE profileviewers.profile_id='$id' and profileviewers.visitor_id=usertable.id  

order by profileviewers.visittime DESC limit $start, $step") or die(mysql_error());

Lars_Berg

select profileviewers.visitor_id,
       profileviewers.visittime,
       usertable.nickname,
       usertable.sex,
       usertable.photo,
       portalsession.user
  from profileviewers
  join usertable on profileviewers.visitor_id = usertable.id
  left join portalsession on profileviewers.visitor_id = portalsession.user
 where profileviewers.profile_id = $id  

 order by profileviewers.visittime desc
 limit $start, $step

What should it be, portalsession or session?

lpa

Thank you Lars Berg. The correct name was portalsession (I just shortened all table names in my post to make it more shorter and readable).

The query works now.

lpa

Unfortunately, there is a problem with this query.

Column "user" in "portalsession" contains:
- IP addresses (if users are guests who are not logged in)
and
- User IDs if users are logged in.

If user ID in "usertable" is, for example, "194", this query will select from "portalsession.user" all IP addresses that start with "194.XXX.XXX.XXX"

I tried to fix the problem by replacing:

left join portalsession on profileviewers.visitor_id = portalsession.user

with:

left join portalsession on  profileviewers.visitor_id = CAST(portalsession.user AS CHAR)

but it doesn't help.

Any ideas how to resolve this problem?

Thanks.

lpa

Something strange...

I didn't change anything, but the problem is gone. "portalsession.user" still contains IP address "194.XXX.XXX.XXX", but it's not being selected for user "194" anymore...