Hi Kevin,
Your version is more-a-less the same than what I wrote. The first table
alias is not necessary and the inner join is just a different syntax for
the same thing.
With the several errors in your statement such as inconsistent field
names and semicolons it is hard to tell what the exact problem is, but
by the looks of it your MySQL does not like the subqueries, which would
be no surprise since they were only introduced with MySQL 4.1.
An alternative would be to use PHP to do the SELECT of the maximum dates
and INSERT them into a temporary table, and then INNER JOIN the two
tables. A more experienced MySQL guru might know better ways (I am
also quite new to the matter myself).
The best thing ofcourse would be to convince your ISP to upgrade MySQL.
Rgds
Carl
Kevin J. Bowman wrote:
> Pete and Carl,
>
> Based on the feedback I have gotten back, I have written this query.
>
> SELECT T2.customer,T1.transaction_code,T2.transaction_date
> FROM
> (SELECT name,date,code
> FROM sample_table) AS T1
> INNER JOIN
> (SELECT customer,max(date) 'transaction_date'
> FROM sample_table
> GROUP BY customer) AS T2
> ON (T1.customer=T2.customer;) AND (T1.date=T2.date;)
>
> When I run it on the version 4.0.22 Standard I get
>
> "1064 - You have an error in your SQl syntax. Check the manual that
> corrresponds to your MySql version for the right syntax to use near
> SELECT name,date,code FROM sample_table) AS T1 INNER JOIN (SELECT"
>
> If it is a MySql server problem, I can get a new host, I am not loyal,
> but how would I determine if that is the problem?
>
> Kevin
>
>
[Non-text portions of this message have been removed]
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
