Hello,
thanx for your help. Anyhow this statement does not return the wanted
fields.
I would like to return the website and the signature of the user, but only
if those values are available. There might be none of them available, but
maybe 1 or even both.
this query:
SELECT
w.website,
s.signature
FROM
user_websites
LEFT JOIN user_signature AS s ON s.user_id = '3'
LEFT JOIN user_websites AS w ON w.user_id = '3'
does return all records of the table website (45000!)
Your stmt does join the website table with the comment (in my case
signature) table:
> 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'
But thats not what I want. I do want just to know if the user has a website,
or a signature and then pull them out if they are there.
Thanx for your help,
Andy
----- Original Message -----
From: "Roger Baklund" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL PROTECTED]>
Cc: "andy" <[EMAIL PROTECTED]>
Sent: Monday, May 20, 2002 3:56 PM
Subject: Re: Problem with join syntax
> * 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
>
---------------------------------------------------------------------
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
