JOIN syntax (INNER JOIN), self join 2

[fickbrayne]

I want to find the most recent purchase for each customer(see below) and I tried:

SELECT A.*
FROM mytable A INNER JOIN (
SELECT full_name, Max(entered_when) AS entered_when FROM mytable GROUP BY full_name
) M ON A.full_name=M.full_name AND A.entered_when=M.entered_when
;

I got a syntax error:
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT full_name, Max(entered_when) AS entered_when FROM mytable

My data looks like:
full_name product_purchased entered_when
phil shoes 20050227121012
mary purse 20020212000000
phil socks 20021226101423
mary bra 20020111000000

(I just want to get phil's shoes and mary's purse.)

What should I be doing?

Thanks!!!!!

 

[azzazzello]

umm...Max(entered_when) AS entered_when...

That just doesn't look kosher to me. You are casting the same name for max() as an existing field name.

 

[swampBoogie]

The error message indicates that you are using a version that does not support derived tables, i.e. 4.0 or older. Upgrade to 4.1 or rewrite the query as

SELECT A.*
FROM mytable A JOIN 
mytable B on a.full_name = b.full_name
group by 
a.full_name,a.entered_when, ... -- all columns in mytable
having a.entered_when = max(b.entered_when)

You are casting the same name for max() as an existing field name.

That's no problem. It's only Access that complains about such trifles.

 

[r937]

trifles!! LOL

i love your posts, swampboogie



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[azzazzello]

well wait a minute...ok, I guess comparisons would ignore anything after AS, but what if he were to group by entered_when? How would the database know whether he means the original column or the max()?

 

[swampBoogie]

You mean as in

SELECT A.entered_when , count(*)
FROM mytable A INNER JOIN (
SELECT full_name, Max(entered_when) AS entered_when FROM mytable GROUP BY full_name
) M ON A.full_name=M.full_name AND A.entered_when=M.entered_when
group by entered_when

?

if so, the above statement should cause an error as the column refence entered_when is ambiguous. To avoid this you can qualify the name by either A or M respectively.

Or do you mean the ability (or rather misfeature, only supported by Mysql afaik) to use correlation names in the group by clause?

ok, I guess comparisons would ignore anything after AS

I don't understand what you mean by that.

 

[azzazzello]

SELECT full_name, Max(entered_when) AS entered_when FROM mytable GROUP BY full_name;

In this query, correlation name entered_when is identical to actual column entered when. If he were to group by entered_when, how would Mysql know if he wants to group by the column name or the correlation name, considering he can group by both

 

[fickbrayne]

OH! The bad-old-version problem. Oy vay. Thanks for your help and comments everyone. Sorry I didn't post this morning - deep snow here in Western Massachusetts.

swampBoogie, you are right. I am using MySQL 4.0.18 and as this is on phpwebhosting.com I don't think I'll get the upgrade in a timely fashion as it's just a little old fickbrayne wots askin'.

I tried the code you suggested and I got another error (please see below):
BTW there were more columns: here's the actual list and results:

SELECT A.*
FROM mytable A JOIN
mytable B on a.full_name = b.full_name
group by
a.full_name, a.first_name, a.last_name, a.product_purchased, a.email, a.email2, a.comments, a.entered_when
having a.entered_when = max(b.entered_when);
ERROR 1109: Unknown table 'a' in on clause
mysql>

Note: product_purchased is usually different in different records for the same customer but email addressess also tend to change from time to time.

I'm trying to figure out this join and I realize some might say "hey, why in heck is this data in one table?" ( the answer being 'Because monkeys are supposed to be able to maintain this database, right?' And write the php to load the records)

So I do appreciate your help while I continue my experimentation!!

cheers,
fickbrayne

 

[azzazzello]

SELECT A.*
FROM mytable A JOIN
mytable B on a.full_name = b.full_name

You are selecting it as capital A, and then trying to reference by small-case a. It's case sensitive.

 

[swampBoogie]

if you do

SELECT full_name, Max(entered_when) AS entered_when FROM mytable GROUP BY entered_when

Mysql will give an error as it will use the correlation name and try to group by the entity referencd by that. Other DBMS would see entered_when as a column reference.

So, using a correlation name that is the same as an column name could only cause problems because Mysql has done an incorrect implementation of group by.