List:General Discussion« Previous MessageNext Message »
From:Christoph Kiehl Date:May 1 1999 3:54pm
Subject:Re: How do I join these tables ... ?
View as plain text  
Hi Jules,

>> 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)


Yep, i got it ;) - BUT ;) ... i'm currently using the first solution. The
second one only works as long as there is only one row you want as result.
But in my case there are multiple rows, so I think there is no way doing
this in one select. The second solution would be alright, but as you already
stated, mySQL does no sub-selects yet. So i'll go with the first one and
wait until mySQL supports sub-selects ;)

Thanks a lot for your answer

Christoph

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