?
Solved

SQL - Selecting a range of records from a query with 3 tables

Posted on 2012-09-21
10
Medium Priority
?
877 Views
Last Modified: 2012-09-21
I am trying to get a range of records to display from a query with three tables.  I get the cannnot be bound message in several areas.  I have included the error screen shot.

Any help is appreciated,

SELECT   EmployeeQuestionsSupervisor.QuestionNumber, EmployeeQuestionsSupervisor.Question, EmployeeResponseSupervisor.UserResponse, EmployeeResponseSupervisor.EmployeeID,employee_data.ID
FROM         (SELECT  ROW_NUMBER() OVER (ORDER BY employeeQuestion.QuestionNumber ASC) AS Row,EmployeeQuestionsSupervisor.QuestionNumber, EmployeeQuestionsSupervisor.Question, EmployeeResponseSupervisor.UserResponse, EmployeeResponseSupervisor.EmployeeID,employee_data.ID
FROM EmployeeQuestionsSupervisor INNER JOIN
                      EmployeeResponseSupervisor ON EmployeeQuestionsSupervisor.QuestionNumber = EmployeeResponseSupervisor.QuestionNumber INNER JOIN
                      employee_data ON EmployeeResponseSupervisor.EmployeeID = employee_data.ID) tmp
 WHERE     Row >= 5 AND Row <= 20
Bound.jpg
0
Comment
Question by:JohnMac328
  • 5
  • 4
10 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 38421926
SELECT   tmp.QuestionNumber, tmp.Question, tmp.UserResponse, tmp.EmployeeID,tmp.ID
FROM         (SELECT  ROW_NUMBER() OVER (ORDER BY employeeQuestion.QuestionNumber ASC) AS Row,EmployeeQuestionsSupervisor.QuestionNumber, EmployeeQuestionsSupervisor.Question, EmployeeResponseSupervisor.UserResponse, EmployeeResponseSupervisor.EmployeeID,employee_data.ID
FROM EmployeeQuestionsSupervisor INNER JOIN
                      EmployeeResponseSupervisor ON EmployeeQuestionsSupervisor.QuestionNumber = EmployeeResponseSupervisor.QuestionNumber INNER JOIN
                      employee_data ON EmployeeResponseSupervisor.EmployeeID = employee_data.ID) tmp
 WHERE     Row >= 5 AND Row <= 20
0
 

Author Comment

by:JohnMac328
ID: 38421938
Now it gives this error
Bound2.jpg
0
 
LVL 25

Expert Comment

by:jogos
ID: 38421943
that's because questionNumber is not in the selectlist of the join

edit: sorry it is
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:JohnMac328
ID: 38421960
I changed it to this and gives the same error except with the name  tmp.QuestionNumber instead of employeeQuestion.QuestionNumber

SELECT   tmp.QuestionNumber, tmp.Question, tmp.UserResponse, tmp.EmployeeID,tmp.ID
FROM         (SELECT  ROW_NUMBER() OVER (ORDER BY  tmp.QuestionNumber ASC) AS Row,EmployeeQuestionsSupervisor.QuestionNumber, EmployeeQuestionsSupervisor.Question, EmployeeResponseSupervisor.UserResponse, EmployeeResponseSupervisor.EmployeeID,employee_data.ID
FROM EmployeeQuestionsSupervisor INNER JOIN
                      EmployeeResponseSupervisor ON EmployeeQuestionsSupervisor.QuestionNumber = EmployeeResponseSupervisor.QuestionNumber INNER JOIN
                      employee_data ON EmployeeResponseSupervisor.EmployeeID = employee_data.ID) tmp
 WHERE     Row >= 5 AND Row <= 20
0
 
LVL 25

Expert Comment

by:jogos
ID: 38421962
Your table employeeQuestion is not in the join
FROM EmployeeQuestionsSupervisor 
INNER JOIN   EmployeeResponseSupervisor ON 
INNER JOIN                       employee_data

Open in new window

0
 
LVL 35

Expert Comment

by:YZlat
ID: 38421963
because there are two fields in different tables name questionNumber
0
 
LVL 25

Expert Comment

by:jogos
ID: 38421965
the alias tmp can only be used outside the (), the original tablenames only inside.
0
 

Author Comment

by:JohnMac328
ID: 38421971
Ok - lets backup - here is the original that uses the three tables and is the one I want to select a range

SELECT   EmployeeQuestionsSupervisor.QuestionNumber, EmployeeQuestionsSupervisor.Question, EmployeeResponseSupervisor.UserResponse, EmployeeResponseSupervisor.EmployeeID,
                      employee_data.ID
FROM         EmployeeQuestionsSupervisor INNER JOIN
                      EmployeeResponseSupervisor ON EmployeeQuestionsSupervisor.QuestionNumber = EmployeeResponseSupervisor.QuestionNumber INNER JOIN
                      employee_data ON EmployeeResponseSupervisor.EmployeeID = employee_data.ID
 WHERE     EmployeeResponseSupervisor.EmployeeID = #val(form.ID)#
0
 
LVL 25

Accepted Solution

by:
jogos earned 2000 total points
ID: 38421979
Think your EmployeeQuestion must be EmployeeQuestionsSupervisor

SELECT   tmp.QuestionNumber, tmp.Question, tmp.UserResponse, tmp.EmployeeID,tmp.ID
FROM         (SELECT  ROW_NUMBER() OVER (ORDER BY EmployeeQuestionsSupervisor.QuestionNumber ASC) AS Row,EmployeeQuestionsSupervisor.QuestionNumber, EmployeeQuestionsSupervisor.Question, EmployeeResponseSupervisor.UserResponse, EmployeeResponseSupervisor.EmployeeID,employee_data.ID
FROM EmployeeQuestionsSupervisor INNER JOIN
                      EmployeeResponseSupervisor ON EmployeeQuestionsSupervisor.QuestionNumber = EmployeeResponseSupervisor.QuestionNumber INNER JOIN
                      employee_data ON EmployeeResponseSupervisor.EmployeeID = employee_data.ID) tmp
 WHERE     tmp.Row >= 5 AND tmp.Row <= 20

Open in new window

0
 

Author Closing Comment

by:JohnMac328
ID: 38421991
My apologies, you are correct - been a long week.

Many thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question