* andy <[EMAIL PROTECTED]>
> thank you roger for your reply.
>
> I am wondering how to form the stmt if I do already know the user_id
>
> I tryed this, but it does not work though. Anyhow there has to be a from
> field. But on which table?
>
> SELECT w.website, c.comment
> LEFT JOIN user_websites AS w ON w.user_id = '10'
> LEFT JOIN user_comments AS c ON c.user_id = '10'
That is correct, when you use LEFT JOIN (or any join, actually) you must
select FROM one table, and then JOIN with the other table(s).
Try this:
SELECT w.website, c.comment
FROM user_websites AS w
LEFT JOIN user_comments AS c ON c.user_id = w.user_id
WHERE w.user_id = '10'
Note that the ON expression contains one field from each of the two tables.
This expression is also known as the 'join condition'. It is perfectly legal
to use a constant, like you did, but that will not be possible in most
cases, like if you want to list more than one user.
The above statement will list the website for the user with id '10', even if
he does not have an associated comment, because of the LEFT JOIN. The
following statement would only find website-rows _with_ comments, ie, if
user_id '10' does not have a comment, you will get 0 rows:
SELECT w.website, c.comment
FROM
user_websites AS w,
user_comments AS c
WHERE
c.user_id = w.user_id AND
w.user_id = '10'
This statement does not contain the word JOIN, but it is still a joined
select. The comma between the tables in the FROM caluse means 'INNER JOIN'.
This is the _excact_ same statement:
SELECT w.website, c.comment
FROM
user_websites AS w
INNER JOIN
user_comments AS c
WHERE
c.user_id = w.user_id AND
w.user_id = '10'
...and this is also legal (in versions 3.23.16 and later):
SELECT w.website, c.comment
FROM
user_websites AS w
INNER JOIN
user_comments AS c ON c.user_id = w.user_id
WHERE
w.user_id = '10'
The join condition can be in the WHERE clause, but it is still a join
condition.
I hope this makes things clearer. :)
--
Roger
sql
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
