Hi,
on item two, you need to do a self join, and then select 'having
max(t1.date)=max(t2.date)'
I have a similar query, where I want to select the record with the highest
number of transactions for each client, which is solved by:
SELECT t1.code, u.name, t1.site_code, t1.we_date, Max(t1.expressnet) AS
'max_jobs'
FROM exnet t1, exnet t2, exnet_users u
WHERE t1.code = t2.code AND t1.site_code = t2.site_code AND u.code = t1.code
AND u.site = t1.site_code
GROUP BY u.name, t1.we_date
HAVING (Max(t1.expressnet)=Max(t2.expressnet))
ORDER BY t1.we_date DESC, max_jobs DESC
'exnet' lists number of transactions ('expressnet') per week ('we_date') for
each client ('code'), and this gives me the maximum number of transactions
per client, ordered by date descending.
On item 1 - no this isn't possible in a single statement yet.
Hope this helps
Quentin
-----Original Message-----
From: Richard Creech - DreamRiver.com [mailto:[EMAIL PROTECTED]]
Sent: Friday, 9 March 2001 2:45 p.m.
To: [EMAIL PROTECTED]
Subject: updating table 1 where table 2 condition? & join
Hello All,
Two items:
1. UPDATING TABLE 1 WHERE TABLE 2 CONDITION
Is it still true I cannot update table 1 on a table 2 field condition?
example: UPDATE member SET member.acluser='6' where payment.memberexpires <
'2001-03-09'
... big sigh ...
2. JOIN SYNTAX
I need help with a specific join syntax. I have two tables with a one to
many relationship. Of the "many", I only want the most recent record. I need
the sql statement which will return only the most recent records in this
instance:
SELECT * FROM member, payment where member.memberid=payment.paymentid ORDER
BY payment.number DESC
... except this query will select all payment records and I only want to
select the records where payment.number (an autoincrement field) is the
highest value for the member. Basically I want to find out if the member has
an expired membership without selecting all the payment records. Only the
most recent payment.number is wanted.
Thank you for any assistance you can render.
BTW, my users very much enjoy the speed and reliability of mysql. Hundreds
of developers download phpYellow Pages every week for this reason. There is
growing acceptance of mySQL as a viable database alternative and I am happy
to see this. Keep up the good work.
Kind Regards,
Richard Creech
[EMAIL PROTECTED]
250.744.3350 Pacific Time Canada
Create your own Yellow Pages Directory with phpYellow Pages
Now Shipping a Premium Version with an Advanced Search module!
http://www.dreamriver.com/phpYellow/
phpEasySQL - connect with your mySQL database using a browser and one php
file - free
http://www.dreamriver.com/software/
---------------------------------------------------------------------
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
The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended
recipient, you are asked to respect that confidentiality and not
disclose, copy or make use of its contents. If received in error
you are asked to destroy this email and contact the sender immediately.
Your assistance is appreciated.
---------------------------------------------------------------------
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
