On 30 May 2001, at 10:14, Paul DuBois wrote:
> At 9:41 AM -0700 5/30/01, Richard Reina wrote:
> >I am stuck on a select query and was wondering if someone could help.
> > I have I've written a database app. that helps me run my business
> >(trucking). I need however to write a query that shows me all of the
> >loads that are delivered but not billed (invoiced). Which means that
> >I have to select the loads that are delivered but do not have an
> >entry in the INVOICED table -- since an entry is made in the INVOICED
> >table whenever a load is billed.
> >
> >I know the query below won't work. Can someone please help me fix
> >it?
> >
> >SELECT l.load_no l.date FROM loads l, invoiced i
> >WHERE l.dlvr_date > 0
> >AND l.load_no != i.load_no
>
> This is a job for LEFT JOIN.
>
> SELECT l.load_no, l.date
> FROM loads l LEFT JOIN invoiced i ON l.load_no = i.load_no
> WHERE i.load_no IS NULL
Can you in sql also have FROM table_a, table_b LEFT JOIN table_c
ON .... WHERE
For example my query is
my $query = "SELECT message.message_id, message.user,
message.for_user, message.sent_date_time, message.subject,
message.id
FROM subject_project, message LEFT JOIN read_message ON
message.message_id = read_message.message_id
WHERE read_message.user = '$user' AND
message.sent_date_time > $new_messages_from_date AND
message.id = subject_project.id
AND subject_project.location_id = 1
AND read_message.user IS NULL ORDER BY
message.sent_date_time" ;
Until now I have just selected the messages that have been sent for
the past 30 days. Then checking for each message with another
query have they read the message or not. If not display it.
The solution suggested by Paul worked fine for another part of my
message system where I don't need to the subject_project table.
An alternative would be to keep the location_id field also in the
message table. Any ideas are welcome.
Scott
_________________________
scott alexander
tietoverkkosuunnittelija
humak amk - finland
+358(0)407505640
---------------------------------------------------------------------
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
