List:General Discussion« Previous MessageNext Message »
From:Jules Bean Date:May 1 1999 2:06pm
Subject:Re: How do I join these tables ... ?
View as plain text  
Christoph Kiehl wrote:
> 
> Hi all,
> 
> I don't know if this join is possible with one select statement ...
> 
> table1:
> 
> id    name
> -------------
> 1    foo
> 2    bar
> 
> table2
> 
> id2    id    val
> -----------------
> 1    2    1
> 2    2    2
> 1    2    3
> 1    3    1
> 2    3    2
> 
> Now I need the max value of val together with id2 from table2 joined with
> table1 on id where id2=2. This should look like this.
> 
> name    id2    val
> ---------------------
> foo    2    3
> 
> My problem is how to get the id2 of MAX(val)? Any ideas? Or am I trying
> something impossible?

It is possible, but it's pretty icky.  Fundamentally, this is because
'aggregate' operations like MAX in SQL are a hack. (Especially in most
practical implementations, which only allow one group by, which must be
at the 'outermost' level).

The simplest, two-stage option is

SELECT MAX(val) FROM table2;
SELECT name,id2,val FROM table1,table2 where val='X' and
table1.id=table2.id;

I can understand why you might not like this, though (it's not atomic,
for example).

The 'order-by' hack is to say:

SELECT name,id2,val FROM table1,table2 WHERE table1.id=table2.id ORDER
BY val DESC LIMIT 1;

This is probably the right option in your case.

There is a sub-select approach, but I don't think MySQL does sub-selects
yet..

SELECT name,id2,val FROM table1,table2 WHERE table1.id=table2.id AND val
IN (SELECT MAX(val) from table2);

(All of these are untested, but you should get the idea)

Jules


-- 
/----------------+-------------------------------+---------------------\
|  Jelibean aka  | jules@stripped         |  6 Evelyn Rd        |
|  Jules aka     |                               |  Richmond, Surrey   |
|  Julian Bean   | jmlb2@stripped        |  TW9 2TF *UK*       |
+----------------+-------------------------------+---------------------+
|  War doesn't demonstrate who's right... just who's left.             |
|  When privacy is outlawed... only the outlaws have privacy.          |
\----------------------------------------------------------------------/
Thread
How do I join these tables ... ?Christoph Kiehl29 Apr
  • Re: How do I join these tables ... ?Jules Bean1 May
Re: How do I join these tables ... ?Christoph Kiehl1 May