Need help sorting out my JOIN statements for multiple sub queries [on hold]
up vote
0
down vote
favorite
I will be up front and say I'm aware this is a messy query. The database is not managed by me so some of the relations are long and complex. Then there is the fact that SQL is just something I know a tiny bit about so these reports get pushed on me and I'm not SQL expert.
That said I'm trying to pull in 3 seprate queries for answer to questions we have on a form. I'm not sure how to bind these with the correct join and I have no doubt there is a better way to do it.
Here is my existing Query:
Select Distinct C.client_id,
C.first_name,
C.last_name,
CV.rev_timein,
Q1.answer,
Q2.answer,
Q3.answer
From Clients C,
(
Select C2.client_id as client_id,
Answer.answer as answer
From Clients C2
Inner Join ClientVisit On C2.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C2.client_id, Answer.answer
) Q1,
(
Select C3.client_id as client_id,
Answer.answer as answer
From Clients C3
Inner Join ClientVisit On C3.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C3.client_id, Answer.answer
) Q2,
(
Select C4.client_id as client_id,
Answer.answer as answer
From Clients C4
Inner Join ClientVisit On C4.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C4.client_id, Answer.answer
) Q3
Inner Join ClientVisit CV On C.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where Forms.form_id = '336' And
FormVersion.is_active = '1' And CV.rev_timein >= @param1 And
CV.rev_timein < DateAdd(d, 1, @param2)
Order By C.last_name
The goal is to have the output for each client in the date range formated as such.
Client_ID, First Name, Last Name, Date-Time, Answer1, Answer2, Answer3
sql-server sql ms-sql
asked 14 hours ago
Bryan
111
put on hold as off-topic by DavidPostill♦ 12 hours ago
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "This question is not about computer hardware or software, within the scope defined in the help center." – DavidPostill
If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
0
down vote
favorite
I will be up front and say I'm aware this is a messy query. The database is not managed by me so some of the relations are long and complex. Then there is the fact that SQL is just something I know a tiny bit about so these reports get pushed on me and I'm not SQL expert.
That said I'm trying to pull in 3 seprate queries for answer to questions we have on a form. I'm not sure how to bind these with the correct join and I have no doubt there is a better way to do it.
Here is my existing Query:
Select Distinct C.client_id,
C.first_name,
C.last_name,
CV.rev_timein,
Q1.answer,
Q2.answer,
Q3.answer
From Clients C,
(
Select C2.client_id as client_id,
Answer.answer as answer
From Clients C2
Inner Join ClientVisit On C2.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C2.client_id, Answer.answer
) Q1,
(
Select C3.client_id as client_id,
Answer.answer as answer
From Clients C3
Inner Join ClientVisit On C3.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C3.client_id, Answer.answer
) Q2,
(
Select C4.client_id as client_id,
Answer.answer as answer
From Clients C4
Inner Join ClientVisit On C4.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C4.client_id, Answer.answer
) Q3
Inner Join ClientVisit CV On C.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where Forms.form_id = '336' And
FormVersion.is_active = '1' And CV.rev_timein >= @param1 And
CV.rev_timein < DateAdd(d, 1, @param2)
Order By C.last_name
The goal is to have the output for each client in the date range formated as such.
Client_ID, First Name, Last Name, Date-Time, Answer1, Answer2, Answer3
sql-server sql ms-sql
asked 14 hours ago
Bryan
111
put on hold as off-topic by DavidPostill♦ 12 hours ago
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "This question is not about computer hardware or software, within the scope defined in the help center." – DavidPostill
If this question can be reworded to fit the rules in the help center, please edit the question.
1
I don't see the point of these subqueries. They seem all to select exactly the same records. And what are you doing if you have another number of answers? This does not seem to be a flexible solution. I would solve the problem in the front-end and create a grouped report by grouping on questions or answers.
– Olivier Jacot-Descombes
14 hours ago
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I will be up front and say I'm aware this is a messy query. The database is not managed by me so some of the relations are long and complex. Then there is the fact that SQL is just something I know a tiny bit about so these reports get pushed on me and I'm not SQL expert.
That said I'm trying to pull in 3 seprate queries for answer to questions we have on a form. I'm not sure how to bind these with the correct join and I have no doubt there is a better way to do it.
Here is my existing Query:
Select Distinct C.client_id,
C.first_name,
C.last_name,
CV.rev_timein,
Q1.answer,
Q2.answer,
Q3.answer
From Clients C,
(
Select C2.client_id as client_id,
Answer.answer as answer
From Clients C2
Inner Join ClientVisit On C2.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C2.client_id, Answer.answer
) Q1,
(
Select C3.client_id as client_id,
Answer.answer as answer
From Clients C3
Inner Join ClientVisit On C3.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C3.client_id, Answer.answer
) Q2,
(
Select C4.client_id as client_id,
Answer.answer as answer
From Clients C4
Inner Join ClientVisit On C4.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C4.client_id, Answer.answer
) Q3
Inner Join ClientVisit CV On C.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where Forms.form_id = '336' And
FormVersion.is_active = '1' And CV.rev_timein >= @param1 And
CV.rev_timein < DateAdd(d, 1, @param2)
Order By C.last_name
The goal is to have the output for each client in the date range formated as such.
Client_ID, First Name, Last Name, Date-Time, Answer1, Answer2, Answer3
sql-server sql ms-sql
asked 14 hours ago
Bryan
111
I will be up front and say I'm aware this is a messy query. The database is not managed by me so some of the relations are long and complex. Then there is the fact that SQL is just something I know a tiny bit about so these reports get pushed on me and I'm not SQL expert.
That said I'm trying to pull in 3 seprate queries for answer to questions we have on a form. I'm not sure how to bind these with the correct join and I have no doubt there is a better way to do it.
Here is my existing Query:
Select Distinct C.client_id,
C.first_name,
C.last_name,
CV.rev_timein,
Q1.answer,
Q2.answer,
Q3.answer
From Clients C,
(
Select C2.client_id as client_id,
Answer.answer as answer
From Clients C2
Inner Join ClientVisit On C2.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C2.client_id, Answer.answer
) Q1,
(
Select C3.client_id as client_id,
Answer.answer as answer
From Clients C3
Inner Join ClientVisit On C3.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C3.client_id, Answer.answer
) Q2,
(
Select C4.client_id as client_id,
Answer.answer as answer
From Clients C4
Inner Join ClientVisit On C4.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And
FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And
ClientVisit.rev_timein < DateAdd(d, 1, @param2)
GROUP BY C4.client_id, Answer.answer
) Q3
Inner Join ClientVisit CV On C.client_id = ClientVisit.client_id
Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =
ClientVisit.clientvisit_id
Left Join Question On Question.question_id = SavedVisitAnswer.question_id
Left Join Category On Question.category_id = Category.category_id
Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id
Left Join Forms On Forms.form_id = FormVersion.form_id
Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id
Where Forms.form_id = '336' And
FormVersion.is_active = '1' And CV.rev_timein >= @param1 And
CV.rev_timein < DateAdd(d, 1, @param2)
Order By C.last_name
The goal is to have the output for each client in the date range formated as such.
Client_ID, First Name, Last Name, Date-Time, Answer1, Answer2, Answer3
sql-server sql ms-sql
sql-server sql ms-sql
asked 14 hours ago
Bryan
111
asked 14 hours ago
Bryan
111
asked 14 hours ago
Bryan
111
asked 14 hours ago
Bryan
111
asked 14 hours ago
Bryan
111
111
put on hold as off-topic by DavidPostill♦ 12 hours ago
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "This question is not about computer hardware or software, within the scope defined in the help center." – DavidPostill
If this question can be reworded to fit the rules in the help center, please edit the question.
put on hold as off-topic by DavidPostill♦ 12 hours ago
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "This question is not about computer hardware or software, within the scope defined in the help center." – DavidPostill
If this question can be reworded to fit the rules in the help center, please edit the question.
1
I don't see the point of these subqueries. They seem all to select exactly the same records. And what are you doing if you have another number of answers? This does not seem to be a flexible solution. I would solve the problem in the front-end and create a grouped report by grouping on questions or answers.
– Olivier Jacot-Descombes
14 hours ago
add a comment |
1
I don't see the point of these subqueries. They seem all to select exactly the same records. And what are you doing if you have another number of answers? This does not seem to be a flexible solution. I would solve the problem in the front-end and create a grouped report by grouping on questions or answers.
– Olivier Jacot-Descombes
14 hours ago
1
1
I don't see the point of these subqueries. They seem all to select exactly the same records. And what are you doing if you have another number of answers? This does not seem to be a flexible solution. I would solve the problem in the front-end and create a grouped report by grouping on questions or answers.
– Olivier Jacot-Descombes
14 hours ago
I don't see the point of these subqueries. They seem all to select exactly the same records. And what are you doing if you have another number of answers? This does not seem to be a flexible solution. I would solve the problem in the front-end and create a grouped report by grouping on questions or answers.
– Olivier Jacot-Descombes
14 hours ago
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
I don't see the point of these subqueries. They seem all to select exactly the same records. And what are you doing if you have another number of answers? This does not seem to be a flexible solution. I would solve the problem in the front-end and create a grouped report by grouping on questions or answers.
– Olivier Jacot-Descombes
14 hours ago