FROM....AS with join - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

FROM....AS with join

Author

Topic

timark
Starting Member

11 Posts

Posted - 2007-12-23 : 12:43:49

I can't figure out the correct syntax to use a FROM...AS statement with a JOIN.

I want to sum values from a table into the appropriate WBS structure elements from another table. Example tables and structure...

....

-WBStable-
WBSnum, WBStitle
A, A Title
AA, AA Title
AAA, AAA Title
AAB, AAB Title
AB, AB Title
B, B Title

-DataTable-
WBSparent, CAID, Month, Year, Data
AAA, 357-605, 1, 2007, 10
AAA, 357-605, 2, 2007, 15
AAA, 357-605, 1, 2006, 10
AAA, 357-605, 2, 2006, 25
AAB, 357-705, 1, 2007, 10
AAB, 357-705, 2, 2007, 15
AAB, 357-705, 1, 2006, 10
AAB, 357-705, 2, 2006, 25
AB, 367-105, 1, 2007, 20
AB, 367-105, 3, 2006, 5

....

What I'm trying to do is sum the appropriate Data elements into the appropriate WBS elements. I want a result set that looks like...

....

WBSelement, WBStitle, Year, Data
A, A Title, 2007, 70
AA, AA Title 2007, 50
AAA, AAA Title 2007, 25
etc.

....

When I do the following query, it runs successfully, but the data sums together from all years, and I want the years to sum separately.

....

Select WBStable.WBSelement, WBStable.WBStitle, DataTable.Year,
(SELECT sum(Data) from DataTable
where left(WBSparent, len(WBStable.WBSelement))
= WBStable.WBelement) AS Data
FROM WBStable
FULL JOIN WBStable.WBSelement = DataTable.WBSparent
GROUP BY WBStable.WBSelement, WBStable.WBStitle, DataTable.Year

....

So..I write the table as follows, and get an error that says 'The multi-part identifier "tablename.fieldname" could not be bound.' for each field in the main select statement except for the subquery....

....

Select WBStable.WBSelement, WBStable.WBStitle, DataTable.Year,
(SELECT sum(Data) from DataTable AS sb
where left(WBSparent, len(WBStable.WBSelement))
= WBStable.WBelement AND
sb.Year = ob.Year) AS Data
FROM WBStable AS ob
FULL JOIN WBStable.WBSelement = DataTable.WBSparent
GROUP BY WBStable.WBSelement, WBStable.WBStitle, DataTable.Year

....

What IS the correct syntax for a FROM...AS statement using a JOIN???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-23 : 13:10:13

I think you can do this with an INNER JOIN. try this & see if it gives you desired result (i've not tested this as i'm checking this from home):-

Select ob.WBSelement, ob.WBStitle, dt.Year,
sum(dt.Data) AS Data
FROM WBStable AS ob
INNER JOIN DataTable dt
ON left(dt.WBSparent, len(ob.WBSelement))
= ob.WBelement
GROUP BY ob.WBSelement, ob.WBStitle, ob.Year

timark
Starting Member

11 Posts

Posted - 2007-12-23 : 13:36:22

I had tried that, and I've forgotten what the results were, but it didn't give me the results I expected. I've figured THIS little problem out, though, actual code follows. I have to get into the habit of properly discriminating tables for complex queries, even ones that don't need it. It would make debugging a little easier. Let's go see what problems crop up next.

This learning process is both fascinating and frustrating. I think I'm doing pretty well for a cost/schedule/risk analyst. That's right - I'm not even a developer - but I did stay at a Holiday Inn Express.....

SELECT obs.AsOfDate, ob.ProgramID, ob.ContractID, ob.WBSID, ob.WBSLevel, ob.WBSTitle,
ob.WBSDescr
,(SELECT sum(Scr) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as Scr
,(SELECT sum(Scm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as Scm
,(SELECT sum(Pcr) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as Pcr
,(SELECT sum(Pcm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as Pcm
,(SELECT sum(Acr) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as Acr
,(SELECT sum(Acm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as Acm
,(SELECT sum(Ecr) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as Ecr
,(SELECT sum(Ecm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as Ecm
,(SELECT sum(BAC) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as BAC
,(SELECT sum(LRE) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as LRE
,(SELECT sum(ETC) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as ETC
,(SELECT sum(S3cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as S3cm
,(SELECT sum(P3cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as P3cm
,(SELECT sum(A3cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as A3cm
,(SELECT sum(E3cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as E3cm
,(SELECT sum(S6cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as S6cm
,(SELECT sum(P6cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as P6cm
,(SELECT sum(A6cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as A6cm
,(SELECT sum(E6cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as E6cm
,(SELECT sum(S1cr) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as S1cr
,(SELECT sum(S1cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as S1cm
,(SELECT sum(P1cr) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as P1cr
,(SELECT sum(P1cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as P1cm
,(SELECT sum(A1cr) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as A1cr
,(SELECT sum(A1cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as A1cm
,(SELECT sum(E1cr) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as E1cr
,(SELECT sum(E1cm) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as E1cm
,(SELECT sum(BAC1) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as BAC1
,(SELECT sum(LRE1) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as LRE1
,(SELECT sum(GEAC) FROM CAData sb Where left ( sb.WBSParent , len(ob.WBSID)) like ob.WBSID and sb.AsOfDate = obs.AsOfDate) as GEAC

FROM WBSelements ob
INNER JOIN CAData obs ON ob.WBSID = obs.WBSParent
GROUP BY ob.ProgramID, ob.ContractID, ob.WBSID, ob.WBSLevel, ob.WBSTitle, ob.WBSDescr, obs.AsOfDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-23 : 13:56:44

the join condition here is different.Thats why i guess you didnt get desired result...
ON ob.WBSID = obs.WBSParent
( i guess WBSID is same as WBSelement)
wont give you result

use
ON left(dt.WBSparent, len(ob.WBSelement))
= ob.WBelement
which also takes that which has starting pattern like other field

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources