The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Simple SQL Help Please.

What is the correct way to do the following query:

I have a table with the columns ID, User.  I have a table that has a many to one relationship that has date, balance, and userId.

I just want to join the two, getting the users and the most current balances.  Don't know how to do it aside from programmatically.  Which is bad. :-)  Any help is much appreciated.
Vince Send private email
Wednesday, March 01, 2006
 
 
Maybe something like this:

SELECT a.user, max(b.date), b.balance
from a Users left join b balances
on a.id = b.userid
group by a.user, b.date
Herbert Sitz Send private email
Wednesday, March 01, 2006
 
 
select u.user, b.balance
from usertbl u
inner join balancetbl b on b.ID = a.userID
where not exists
(select 1 from balancetbl b2 where b2.id = b.id and b2.date > b.date)

That, with any errors fixed, should do it.
clcr
Wednesday, March 01, 2006
 
 
Sorry, there are those times when I scribble down something embarrassingly bad.  This is one of those times. 

clcr's answer looks good to me, probably the one you'd find in a textbook.
Herbert Sitz Send private email
Wednesday, March 01, 2006
 
 
Are analytic (windowing) functions available on your platform?
David Aldridge Send private email
Wednesday, March 01, 2006
 
 
Here's another possibility, depending on the query optimizer it might be slower or faster than other possibilities

SELECT a.id, b.balance
  WHERE a.id = b.id
  AND b.date in
    (SELECT max(b.date) WHERE
        b.id = a.id)

This is assuming I remember the MAX function correctly, as well.
dot for this one Send private email
Monday, March 06, 2006
 
 
Get The Guru's Guide to Transact-SQL. I don't say it to be a smart ass, because I. like you, would trip on relatively simple SQL problems. After reading this book over a weekend, though, things got a lot clearer. (BTW, if you are not using SQL server, then there's probably a comporable book on your database).


http://www.amazon.com/gp/product/0201615762/sr=8-2/qid=1141737973/ref=pd_bbs_2/103-9800071-7082220?%5Fencoding=UTF8
Hash
Tuesday, March 07, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz