SQL Query
I am getting error for this query. How to solve?
SELECT {User}.[Email]
FROM {User}
INNER JOIN {Students} ON
{Students}.[StudentStatusId] = {StudentStatus}.[Id]

INNER JOIN {Student_Department_Extd} ON
{Student_Department_Extd}.[StudentId] = {Students}.[Id]

INNER JOIN {Department} ON
{Department}.[Id] ={Student_Department_Extd}.[DepartmentId]
 
INNER JOIN {UserDepartmentId} ON
{UserDepartmentId}.[DepartmentId] = {Department}.[Id]

INNER JOIN {User} ON
{User}.[Id] = {UserDepartmentId}.[UserId]

WHERE (({Students}.[StudentStatusId] = 4) 
OR ({Students}.[StudentStatusId] = 2));
Screenshot20210915140605.png

mvp_badge
MVP

Hi Siva D,


You're joining two times with the entity {User} and SQL engine needs to make a distinction between them.

The error message also mentions you're using a {StudentStatus}.[Id] without having joined with StudentStatus.

Can you try the query below? I think this is what you want to do.


SELECT {User}.[Email]
FROM {Students}
INNER JOIN {StudentStatus} ON
{Students}.[StudentStatusId] = {StudentStatus}.[Id]

INNER JOIN {Student_Department_Extd} ON
{Student_Department_Extd}.[StudentId] = {Students}.[Id]

INNER JOIN {Department} ON
{Department}.[Id] ={Student_Department_Extd}.[DepartmentId]
 
INNER JOIN {UserDepartmentId} ON
{UserDepartmentId}.[DepartmentId] = {Department}.[Id]

INNER JOIN {User} ON
{User}.[Id] = {UserDepartmentId}.[UserId]

WHERE {Students}.[StudentStatusId] = 4 
OR {Students}.[StudentStatusId] = 2


Kind Regards,
João

Thank you João.

Hi Siva,

you're joining twice {User} table. 

For one of the joins, you need to use an alias

  • INNER JOIN {User} UserDep ON
    UserDep.[Id] = {UserDepartmentId}.[UserId]
mvp_badge
MVP

That won't entirely fix the issue as it won't fix the first part of the error message shared by Siva.


Right, it was a missing another table join.

Hi Siva,

Since you've got the tags 'Beginner' and 'Aggregate' added to your question, I'm going to say it:

Although I'm a big fan of 'real' SQL queries, it's best to use Aggregates where possible. One advantage is maintainability, but it also performs better than an SQL Query.
Of course I don't know how you're planning to extend this specific query, but as it is now it's easier to use an aggregate.

Kind regards,
Lennart

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.