need help on a SQL statement

[Anon]

I'm working to build my own forum. I have two tables:
topic: ..., topicid
msgstatus: topicid, userid, interest
in which 'interest' is the status of user to that msg: high, normal, ignore.
Now the problem is: I need to have a sql statement to get a record from both tables. Return fields must be topic. and msgstatus.interest.
The query will get a record with same topicid on both tables, userid=$userid
This is what I did:
SELECT topic.,interest FROM topic left join msgstatus using (topicid) WHERE topic.topicid=2 and msgstatus.userid=1;
Now, it's easy when there is always a record in msgtatus with same topicid as in topic table. When there is no record in msgtatus like that, there result should be all fields in topic table and 'interest' field must be null. Well, how to to do that? Because the Sql statement above will return none in that case. Please help.

[tomhath]

The problem is with your "where" clause. It controls what is returned in the result set after the other joins, so none of the resulting rows with null userid are returned (because you said you only want the ones for userid=1). To fix it you have to move that condition up into the "join" as follows:

SELECT topic.*,interest
FROM topic left join msgstatus
ON topic.topicid=msgstatus.topicid
AND msgstatus.userid=1
WHERE topic.topicid=2

[Anon]

sweet!! It works. Thanks.