Need help with a join query using aggregate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with a join query using aggregate

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-06-08 : 09:12:18

I have a query in which I am using 2 tables but 3 joins (so a self-join to the same table and then use of another table).

The tables are as follows:

Table1:
Date ID Value

Table 2:
ID ID2 ID3

I am using a self join from TableA as follows:

select a.Date, min(b.Date), a.ID
from Table1 a,
Table1 b
where a.ID = b.ID
and b.Date>a.Date
group by a.Date, a.ID, a.Value

this returns me each record per ID in TableA along with its immediate successive observation (which is something I require).

My question is, I would like to return the same values in the select list above but also with ID2 and ID3 from Table2. In other words, I would like:

select a.Date, min(b.Date), a.ID, c.ID2, c.ID3

but I don't know how to integrate Table2 data into this query. I know that I could just go with the first query, put the results into a tmp table and then perform a separate join of these results with Table2, but this seems redundant and inefficient to me. Isn't there a way to integrate these to into the same query returning the results I'm looking for above?

So far, I'm trying:

select a.Date, min(b.Date), a.ID, c.ID2, c.ID3
from Table1 a,
Table1 b,
Table2 c
where a.ID = b.ID
and a.ID = c.ID
and b.Date>a.Date
group by a.Date, a.ID, a.Value, c.ID2, c.ID3

but this isn't working (probably because my group by clause is wrong).

If anyone has anyone suggestions, I would really appreciate it.

Thanks in advance!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-08 : 10:29:40

I don't know if this works because you didn't provide any (working) DDL/DML. Please follow Brett's excellent posting instructions: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


select	a.Date, minDate, a.ID, c.ID2, c.ID3
from	(
	select a.Date, min(b.Date) minDate, a.ID
	from	Table1 a
	JOIN	Table1 b ON a.ID = b.ID
			AND b.Date>a.Date
	group by a.Date, a.ID, a.Value
	) a
join	Table2 c ON a.ID = c.ID

Be One with the Optimizer
TG

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-06-08 : 10:41:57

Hi TG,

I believe this may have been what I was looking for.

Thank you for your reply. I'll try and post with DDL & DML next time, per Brett's posting instructions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources