simplified version of query help

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)

simplified version of query help

Author

Topic

cragi
Starting Member

14 Posts

Posted - 2004-09-12 : 10:41:39

I guess I tried to oversimplify the problem too much.
There is an outer query containing about 10 tables joined. For each record in one of the joined tables, say "MasterTable", I want to see if any rows from a related (by "GroupID") table will match a set of criteria, say we check its "Value", and of those matching, I want the one with the lowest "Priority", or to ignore the entire row altogether.

MasterTable (MT)
MTID GroupID
0001 1000
0002 1001

SlaveTable (ST)
STID GroupID GradeID Priority Value
0087 1000 X 0 87
0088 1000 Y 2 12
0089 1000 Z 1 40
0090 1001 A 0 124
0091 1001 A 0 76
0092 1001 B 1 850

so lets say the value I want to match is <50
so, for the MasterTable, I would be reading the first row (0001), then try to return the GradeID whose GroupID matches (down to records 0087,0088,0089) and whose value is less than 50 (down to records 0088,0089) and now I'll take the first record available that has the lowest priority (0089)

Here is my attempt:
select ..... MT.* .....from ..... join MasterTable MT .... join (select TOP 1 GradeID,GroupID from SlaveTable Where Value<50 ORDER BY Priority ASC ) ST on MT.GroupID=ST.GroupID

The problem above was that the inner select evaluated before I could use the constraint limiting it to the rows matching the outer table, but I can't do the constraint on the inner table table either, because it doesnt recognize the outer table

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-12 : 10:52:27

will this help?

select ..... MT.* .....
from .....
join MasterTable MT ....
join (select min(Priority) as Priority, GroupID from SlaveTable Where Value<50 GROUP BY GroupID) ST on MT.GroupID = ST.GroupID

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-12 : 11:43:00

or to get the rows
select *
from mt
join st
on st.groupid = mt.groupid
where st.priority = (select min(priority) from st where st.groupid = mt.groupid and st.value < 50)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources