Subquery Question

[Thorsten Hohage]

Hi Fabian,

On 2008-01-03, at 15:26, Fabian Eschrich wrote:

> 	select sum(amount) as amount,addresses.id,addresses.type as
> addresstype,searchcode,name1,name2,customernumber
> 	from accounting
> 	left outer join addresses on accounting.addressid = addresses.id
> 	group by accounting.addressid
>
> As I learned, this is not sql conform, because of mixing aggregate  
> fields
> and normal fields is not allowed.


Why not try

select
sum(amount) as amount,

addresses.id,

min(addresses.type) as addresstype,
min(searchcode),
min(name1),
min(name2),
min(customernumber)

from accounting
	left outer join addresses on accounting.addressid = addresses.id
	group by accounting.addressid


because all this fields used in the min() should be the same for ALL  
the account rows!?!?


> So I thought the easiest way to solve this should be a simple  
> subquery.
>
> 	select id,searchcode,type,name1,name2,customernumber,
> 	(SELECT SUM(Amount) from accounting where accounting.addressid = ID)
> 	from addresses
>
> But on this one is not correct - gives me only 0,00 for everyone. Am  
> I on
> the right way to use a subquery?
> I want to get this list with one query. Maybe I should use a  
> different way?


Issue here is, that currently Valentina didn't support correlative sub- 
queries, I'm afraid.

regards

Thorsten Hohage
--
objectmanufactur.com - Hamburg,Germany