3 tables one select statements help plz

[littleB]

Hi,

I have 3 tables like these:
Table 1:People
Id Lname Fname Gender


Table2:Address_Info
Id Address City State Zip


Table3: Expense
Id Date Amount Type

I want to get a person with the largest total amount of money spent and return the Fname, Lname, address information and the total amount of all their expenses?

I use this query which does not work:
Select people.Fname, people.Lname, people.Gender, Address_Info.Address, Address_Info.City, Address_Info.State, Address_Info.Zip, SUM(Expense.Amount) AS Amount FROM People, Address_Info, Expense WHERE Amount=(Select SUM(Expense.Amount) from Expense);

please help

Thanks a lot

[Kots]

I tried to create the whole scenario, Hope this helps !!

create table people(id int primary key,lname varchar(30),fname varchar(30),gender char(1))
go

insert into people
select 1,'SAM','PAMELA','F'
insert into people
select 2,'RON','OMMER','M'
insert into people
select 3,'BOB','DENNIS','M'
insert into people
select 4,'PAT','BONNIE','F'

create table Address(id int foreign key references people(id),address varchar(30),city varchar(30),state char(2),zip char(5))
go

insert into Address
select 1,'3 MAIN STREET','FORDS','NJ','08976'
insert into Address
select 2,'4 SMITH STREET','WHIPPANY','NJ','08864'
insert into Address
select 3,'5 ADAMS STREET','ROSELAND','NJ','09876'
insert into Address
select 4,'6 SMITH STREET','NORTH BRUNSWICK','NJ','09876'


create table Expense(id int foreign key references people(id),expense_date datetime,amount money, type varchar(5))
go

--insert multiple expense records for each person
insert into expense
select 1,'1/1/2006',200,'trvl'
insert into expense
select 1,'2/1/2006',400,'food'
insert into expense
select 1,'3/1/2006',300,'gas'

insert into expense
select 2,'1/2/2006',250,'trvl'
insert into expense
select 2,'2/2/2006',375,'food'
insert into expense
select 2,'3/2/2006',225,'gas'

insert into expense
select 3,'1/3/2006',125,'trvl'
insert into expense
select 3,'2/3/2006',200,'food'
insert into expense
select 3,'3/3/2006',350,'gas'

insert into expense
select 4,'1/4/2006',300,'travl'
insert into expense
select 4,'2/4/2006',250,'food'
insert into expense
select 4,'3/4/2006',175,'gas'

select * from people
go
select * from address
go
select * from expense
go

--Here is the query to get a single row with max. expenses
select
p.LNAME,
p.FNAME,
a.ADDRESS,
a.CITY,
a.STATE,
a.ZIP,
sum(e.amount) TOTAL_EXPENSE
from
people p,
address a,
expense e
where
p.id = a.id
and a.id = e.id
group by p.id,p.lname,p.fname,a.address,a.city,a.state,a.zi p
having sum(e.amount) = (select top 1 sum(amount) from expense group by id)

[jkoopmann]

This article might also help, solutions near the bottom of the article.
And it covers the RANK function in Oracle if you are using Oracle.

http://www.databasejournal.com/featu...le.php/3530801

[littleB]

Thank you very much guys for your responses.
I'm using timesten database which it does not like the part
(select top 1 sum(amount) from expense group by id)

so I tried
(select MAX (sum(amount)) from expense group by id)
but timesten returns " 1032: Aggregate function cannot be nested"

I think the select statement posted by Kots is working for other db but not timesten.

thanks a lot