Re: Query problem

From: Thomas R. Hummel <tom_hummel_at_my-deja.com>
Date: 2000/04/04
Message-ID: <8cdi7k$mg2$1@nnrp1.deja.com>#1/1

If you are trying to get the latest record only from the many side then you will want something like this:

select T1.MyField1, T2.MyField2
from OneTable T1
inner join ManyTable T2 on T2.MyID = T1.MyID

                       and T2.UpdatedDate = (select max(UpdatedDate)

    from ManyTable T3 where T3.MyID = T1.MyID)

Of course, you might need to add other criteria or use a left outer join depending on your own criteria, but hopefully you can get the general gist of it.

   -Thomas R. Hummel.

In article <4dqG4.34262$MZ2.451051_at_news1.wwck1.ri.home.com>,   "Ryan Novak" <ryan_nospam_at_nospam.novak.net> wrote:
> Ok, I have two tables that have a one-to-many relationship.
>
> I want to merge both these tables into a single view. No problem.
>
> Now here is where my problem begins. Let's say that this view
 returns two
> records caused by the one-to-many relationship. Each record is unique
> because of the "many" side. How do I get it to return one row? For
> example, I just wanted to get the most recent data from the "many"
 side.
>
> I am using MSSQL 7.0 as a backend. I can do this in a query in
 Access, but
> haven't been able to figure out how to do it in SQL.
>
> ---Ryan
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 04 2000 - 00:00:00 CDT