* andy
> 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!)
Yes... you should have a user table, with one row for each user, and select
FROM that table.
> 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.
You can not find out anything about a 'user' if you don't have a 'user' in
your system... :) You should have a user table, with user id, user name etc.
The user id should be the primary key, and your fields 'user_id' in the
tables user_signatures and user_websites would reffer to the primary key.
If you really don't have/want a user table, try something like this:
(You use quotes around your user_id constants, so I assume it is a string...
it could (should?) be an integer. If it is an integer, use 'int' instead of
'varchar(255)' in the create statement.)
create temporary table tmp_users (user_id varchar(255));
insert into tmp_users values ('3');
SELECT w.website, s.signature
FROM tmp_users
LEFT JOIN user_signature AS s ON s.user_id = tmp_users.user_id
LEFT JOIN user_websites AS w ON w.user_id = tmp_users.user_id
WHERE tmp_users.user_id = '3'
The WHERE clause is not needed here, but I include it because it _is_ needed
if you use a real user table, with multiple rows.
Your first query in this post would probably work if you select FROM
tmp_users, but proper join conditions like in this last query is cleaner,
and easier to expand.
A temporary table is automatically deleted when the connection is closed,
and if multiple users create a temporary table with the same name at the
same time, that's ok with mysql.
--
Roger
---------------------------------------------------------------------
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
