AW: Subquery Question

[Fabian Eschrich]

Hi Thorsten,

absolutly nice idea ;-) I don't thought about this way.
Thank you very much.

Mit freundlichen Grüßen / kindly regards,

Fabian Eschrich
FAESCH Software

info at faesch.de
 
Fon:     +49 (0)37206 889293
Fax:     +49 (0)37206 889294
Skype:   feschrich
SIPgate: 9000760

-----Ursprüngliche Nachricht-----
Von: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] Im Auftrag von Thorsten Hohage
Gesendet: Donnerstag, 3. Januar 2008 22:50
An: Valentina Developers
Betreff: Re: Subquery Question

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


_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina