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.
| Author |
Topic |
|
lanejc
Starting Member
11 Posts |
 Posted - 2002-05-21 : 17:04:45
|
| I need to perform a sub select where the results of the sub-select are going to depend on values form the main select. I have a column of data in table2, and I need to return the SUM of all the particular rows into the main select. So if there is a column called ID in the main table, then I need to do a SUM of the AMOUNT column from the second table based on the ID value from the first table.So can I reference the ID value from the first table in the subselect (i.e. does the sub-select inherit information form the main select)? |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-21 : 17:13:56
|
| post the ddl for the tables (or some made up tables) so we can all be on the same page . . .to me it sounds like the best thing for you to do is to join to a derived table of ID and sum(amount) from table2 grouped by id, such that the on clause of the join would match the id of the derived table to the id of the first table. then use the aliased sumofamount column from the derived table as a column in your outer select statement.you post ddl, I'll post dml . . .<O> |
 |
|
|
lanejc
Starting Member
11 Posts |
Posted - 2002-05-21 : 17:16:31
|
| I figured it ok, thanks though. : )I do have one question. I've returned my Sub-Select as a value TGLAMT and I would like to use that value in my where clause, is this possible? Here's a sample of my SQL statement:SELECT P.CO#, P.SUBDIV, P.POWO#, P.JOB#, P.CSTCDE, P.VEN#, P.ISSDTE, P.POAMT, (SELECT SUM(GLAMT) FROM LEI#BLDG.DETAIL D WHERE D.SUBDIV = P.SUBDIV AND D.JOB# = P.JOB# AND D.CSTCDE = P.CSTCDE) AS TGLAMT, P.BDGAMT, P.VARCDE, P.EXTRA, P.AREA#, J.STGCUR, J.ADDRS, J.STREET, J.PLAN, J.PURCH, V.VENNME, V.VADDL1, V.VADDL2,V.CITY AS VCITY,V.STCODE AS VSTATE, V.ZIP5 AS VZIP5, V.ZIP4 AS VZIP4, A.ARENAM, C.CCDESC, S.SUBNAM FROM LEI#CNSL.VENDOR V, LEI#CNSL.PURORD P LEFT OUTER JOIN LEI#CNSL.JOBMST J ON J.SUBDIV = P.SUBDIV AND J.JOB# = P.JOB# LEFT OUTER JOIN LEI#CNSL.AREA A ON P.AREA# = A.AREA# LEFT OUTER JOIN LEI#CNSL.COSTCD C ON C.CSTCDE = P.CSTCDE LEFT OUTER JOIN LEI#BLDG.SUBDVN S ON S.SUBDIV = P.SUBDIV WHERE J.WRKSHT = 'Y' AND C.WRKSHT = 'Y' AND P.BDGAMT > 0 AND TGLAMT < P.BDGAMT AND J.CO# = '01' AND P.VOIDDT = '' AND P.VEN# <> '' AND P.VEN# = V.VEN#In the WHERE clause, I would like to add something like 'AND TGLAMT < P.BDGAMT' so so that I only get documents that have a TGLAMT less than the BDGAMT. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-21 : 17:23:19
|
| I don't believe you can, however if you use the derived table like I suggested, you could ....<O> |
|
|
|
|
|
|
|
|
Trying to understand sub-selects