Hi.
On Thu 2002-07-25 at 16:44:19 +0100, [EMAIL PROTECTED] wrote:
[...]
> I want to create a query that will select the user from the users
> table and if they have any time data against them (for that day)
> return that also. If there is not data for that day only the user
> data should be returned.
>
> SELECT u.id,u.surname,t.*
> FROM users u LEFT OUTER JOIN times t
> ON (u.id =t.id)
> WHERE u.id = 22;
>
> I get:
> 22|Price|NULL|NULL| NULL| NULL| NULL| NULL| NULL| NULL
>
> Which I could work with but once I add "AND t.date = (current_date)
> I get 0 records returned and I need the users.id an users.surname.
>
> Can anyone think of a way round this?
Unfortunately you did not provide your change in a full example,
therefore I have to guess. I guess you put the additional condition in
the WHERE clause. Because it relates to the times table and you want
to see the users regardless, you have to put it in the ON clause, too:
SELECT u.id, u.surname, t.*
FROM users u
LEFT OUTER JOIN times t ON u.id = t.id AND t.date = [current_date]
WHERE u.id = 22
In other words, whenever you can say "I want to see the content of the
left table (here: user) even if there is no match in the right one
(here: times)", you have to put the condition in question into the ON
clause of the LEFT JOIN instead of the WHERE clause (which would
restrict the end result).
Greetings,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
