Need help getting specific data from tables

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)

Need help getting specific data from tables

Author

Topic

drew22299
Starting Member

26 Posts

Posted - 2009-01-19 : 08:43:10

Hi,

I'm trying to get a small amount of data from lots of tables and I'm confused about what the SQL code should be. Sorry if this question is long, any help will be appreciated

Here are the tables:

BatchedScores
(Contains VisitUID and Score columns)

Visits
BranchUID
Period

Branch
BranchUID
ClientUID

Client
ClientUID

ClientRegion
CleintRegionUID
RegionName
ClientUID

ClientPeriod
ClientPeriodUID
ClientUID
PeriodName

Each Client has a number of branches and each branch has multiple visits. Each visit has a total score and the scores are batched and stored in the BatchedScores table.

I'm trying to get the average scores for each region by periodName, (for example, period name might be jan, feb, march)

Here is the SQL I have so far but it doesn't work:

DECLARE @ClientUID nvarchar(10)
DECLARE @ClientPeriodUID nvarchar(25)

SET @ClientUID = ''
SET @ClientPeriodUID = ''

SELECT t1.Name, t1.PeriodName

FROM (SELECT ClientRegion.Name, ClientPeriod.PeriodName, Visit.Period, Visit.VisitUID,

INNER JOIN BatchedVisitScores ON Visit.VisitUID = BatchedVisitScores.VisitUID
INNER JOIN ClientPeriod ON Visit.Period = ClientPeriod.ClientPeriodUID
INNER JOIN ClientRegion ON Client.ClientUID = ClientRegion.ClientUID
INNER JOIN Branch ON Client.ClientUID = Branch.BranchUID

WHERE Client.ClientUID = @ClientUID
and BatchedVisitScores.BranchUID = (how do I get the branches from the visit records to get the BatchedVisitScores? can't do BatchedVisitScores.BranchUID = Visit.BranchUID is there a similar way?)

) AS t1

GROUP BY t1.PeriodName, t1.Name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 09:07:57

cant you just add a join to visist based on last where condition? (BatchedVisitScores.BranchUID = Visit.BranchUID )

drew22299
Starting Member

26 Posts

Posted - 2009-01-20 : 14:04:05

Thanks for your reply. That's what I thought but it didn't work, can anyone else see why?

drew22299
Starting Member

26 Posts

Posted - 2009-01-21 : 03:32:43

I added another join:

BatchedVisitScores.BranchUID = Visit.BranchUID

But I get this error:

The objects "Visit" and "Visit" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

I tried using an alias for the Visit table but it didn't work.

Any experts out there who can help me with this query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:04:02

this is because you've used Visit table twice in same query. so add aliases like v1,v2 and also change them in field names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources