[ILUG] Re: MySQL question

[Niall O Broin]

Answering my own question for the second time in a week is bad, but my
answer wasn't right, so further clarification was needed. I asked 

> Given two tables

> mysql> describe Customers;
> +--------------+-------------+------+-----+-----------+----------------+
> | Field        | Type        | Null | Key | Default   | Extra          |
> +--------------+-------------+------+-----+-----------+----------------+
> | Username     | varchar(32) |      | UNI |           |                |
> | Password     | varchar(32) | YES  |     | NULL      |                |
> | ID           | smallint(6) |      | PRI | NULL      | auto_increment |
> | Name         | varchar(50) |      |     |           |                |
> +--------------+-------------+------+-----+-----------+----------------+
> 9 rows in set (0.00 sec)
>  
>  mysql> describe Deliveries;
>  +-------------+---------------+------+-----+------------+-------+
>  | Field       | Type          | Null | Key | Default    | Extra |
>  +-------------+---------------+------+-----+------------+-------+
>  | Customer_ID | smallint(6)   |      | PRI | 0          |       |
>  | Picture_ID  | int(11)       |      | PRI | 0          |       |
>  | Date        | date          |      | PRI | 0000-00-00 |       |
>  | When        | timestamp(14) | YES  |     | NULL       |       |
>  +-------------+---------------+------+-----+------------+-------+
>  4 rows in set (0.00 sec)                 
> 
> How do I get a listing of Customers with the date of each customer's latest
> delivery, ordered by the date ? I tried 
> 
> select Name, max(Date) as Max from Customers, Deliveries order by Max; 
> 
> but that tells me that 
> 
> Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is
> illegal if there is no GROUP BY clause
> 
> so I tried various combinations of GROUP BY with no great success. Can I do I
> do this and if so, how (and given that it's MySQL, I hope the answer doesn't
> involve subselects :-( )

I get an answer from (I though had tried this before my first question)

select ID, Name, max(Date) as max from Customers, Deliveries where
ID=Customer_ID group by ID order by max;

(I group by ID because ID is unique - Name is not)

but what that returns me is not correct - I get 85 rows returned, yet 

select count(distinct(Customer_ID)) from Deliveries;

returned 91.  There's something decidedly strange there - that group query
should surely return 91 rows. A little (no, a lot) of investigation revealed
that I had some Delivery records for deleted Customers, although the Perl
code which deletes a Customer also deletes all associated Delivery records,
or should :-), and the log doesn't show any errors. Ah well, this whole
thing has led to a cleanup of the data, which can't be all bad. When is
MySQL getting triggers :-) ?

Thanks to John Diamond who through his celebratory beer haze pointed out that

> it max date grouped by customerid 

and later pointed out that I'd better group by ID as Name may not be unique
(it wasn't (customer requirement), and that bit me for a while). Of course
to add to the fun :-) I did't want 91 rows returned  - I wanted 179 i.e.
one for each customer, even those which don't have any deliveries recorded.
A little RTFM led me to the conclusion that a LEFT JOIN is what I needed so
I came up with 

select ID, Name, max(Date) as max from Customers left join Deliveries
on ID=Customer_ID  group by ID order by max

which does the business. It's definitely time to pick up that SQL book again
and try to read the bloody thing :-) esp. WRT joins. For instance, after
solving my problem I got curious and I tried all of these

select ID, Name, max(Date) as max from Customers inner join Deliveries on
ID=Customer_ID group by ID order by max,ID;

select ID, Name, max(Date) as max from Customers natural join Deliveries
where ID=Customer_ID  group by ID order by max,ID;

select ID, Name, max(Date) as max from Customers, Deliveries where
ID=Customer_ID  group by ID order by max,ID;

all of which give me the exact same output. Why do I sometimes need an ON
clause, and sometimes WHERE will do ? Oh bugger, back to that book :-( This
is obviously why good DBAs command such high rates :-)





Niall