Can an expert please help me with this query? 1

[aspdotnetuser]

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, Visits, Branch, Client, ClientRegion, ClientPeriod

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.

Can someone help me modify this query to get the average scores as percentages for each region by periodName, (for example, period name might be jan, feb, march and region south, south west etc)

Here is the SQL I have so far:

[blue]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 = Visit.BranchUID <--- This causes an error
) AS t1

GROUP BY t1.PeriodName, t1.Name[/blue]

Error message: [red]The objects "Visit" and "Visit" in the FROM clause have the same exposed names. Use correlation names to distinguish them.[/red]

I tried using aliases for visit but it didn't work.

 

[r937]

are you sure you're using MySQL?

MySQL does not produce error messages like that one

your subquery is missing the FROM keyword and appears to be missing the Visit table

SELECT t1.Name
     , t1.PeriodName 
  FROM (
       SELECT ClientRegion.Name
            , ClientPeriod.PeriodName
            , Visit.Period
            , Visit.VisitUID
         [red]FROM Visit[/red]
       INNER 
         JOIN BatchedVisitScores 
           ON BatchedVisitScores.VisitUID = Visit.VisitUID
          [red]AND BatchedVisitScores.BranchUID = Visit.BranchUID[/red]
       INNER 
         JOIN ClientPeriod 
           ON ClientPeriod.ClientPeriodUID = Visit.Period
       INNER 
         JOIN ClientRegion 
           ON ClientRegion.ClientUID = Client.ClientUID
       INNER 
         JOIN Branch 
           ON Branch.BranchUID = Client.ClientUID
        WHERE Client.ClientUID = @ClientUID 
        ) AS t1 
GROUP 
    BY t1.PeriodName
     , t1.Name

this query can be simplified as follows:

SELECT [blue]DISTINCT[/blue]
       ClientRegion.Name
     , ClientPeriod.PeriodName
  FROM Visit
INNER 
  JOIN BatchedVisitScores 
    ON BatchedVisitScores.VisitUID = Visit.VisitUID
   AND BatchedVisitScores.BranchUID = Visit.BranchUID
INNER 
  JOIN ClientPeriod 
    ON ClientPeriod.ClientPeriodUID = Visit.Period
INNER 
  JOIN ClientRegion 
    ON ClientRegion.ClientUID = Client.ClientUID
INNER 
  JOIN Branch 
    ON Branch.BranchUID = Client.ClientUID
 WHERE Client.ClientUID = @ClientUID

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[aspdotnetuser]

Thanks for your reply and for the simplified code ;-) It's great to get help from someone who has written a book on SQL. But I get an error when I run that query:

[red]The multi-part identifier "Client.ClientUID" could not be bound.[/red]

Is this because Client has been referenced more than once in the joins?

 

[r937]

no, it's because the Client table is missing from the joins

having seen the pattern, do you think you can add it?

and i'm curious, just which DBMS is this?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ingresman]

Rudy,

It's SQL server

error 4104 actually !