SQL error: "expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'"

I had this question after viewing Need Distinct Value from Joined Tables in MSSQL.

With this SQL query, I am trying to match the faculty members with their topics. The user is presented with a dropdown of Research Topics, When selected, the user should be presented with a list of faculty members who are working in research on those topics.

My Query:

SELECT First_Name, Last_Name, Department_Name, suapp.FACULTY.Faculty_UUID, Faculty_Email, Faculty_Title, Faculty_Profile_Link FROM suapp.FACULTY
JOIN (SELECT DISTINCT
suapp.RESEARCH_MATCH.Faculty_UUID
FROM suapp.RESEARCH_MATCH
JOIN suapp.AREA_TOPIC.Area_Topic_Code
ON suapp.RESEARCH_MATCH.Area_Topic_Code
WHERE suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
AND suapp.AREA_TOPIC.Area_Topic_Code = 'CD-a'
) rm ON suapp.FACULTY.Faculty_UUID = rm.Faculty_UUID ORDER BY suapp.FACULTY.Last_Name, suapp.FACULTY.First_Name;

The error:

An expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'.

suapp-research-dashboard-example-deb.pdf
Thank you for any help in this situation.
Debra Turner

Debra Turner

Asked: 2016-11-30

Advertise Here

1 Solution

LVL 38

ste5anSenior DeveloperCommented: 2016-11-30

First of all, please use the embed CODE button..

The issue is a copy'n'paste error. You have simply a WHERE where none is needed. E.g.

SELECT  First_Name ,
        Last_Name ,
        Department_Name ,
        suapp.FACULTY.Faculty_UUID ,
        Faculty_Email ,
        Faculty_Title ,
        Faculty_Profile_Link
FROM    suapp.FACULTY
        JOIN ( SELECT DISTINCT
                        suapp.RESEARCH_MATCH.Faculty_UUID
               FROM     suapp.RESEARCH_MATCH
                        JOIN suapp.AREA_TOPIC.Area_Topic_Code ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
                                                                 AND suapp.AREA_TOPIC.Area_Topic_Code = 'CD-a'
             ) rm ON suapp.FACULTY.Faculty_UUID = rm.Faculty_UUID
ORDER BY suapp.FACULTY.Last_Name ,
        suapp.FACULTY.First_Name;

Open in new window

btw, please use table alias names and qualify all used column names with it.

LVL 111

Ray PaseurCommented: 2016-11-30

I think the JOIN ... ON needs to have some kind of equality expression in the ON clause. This is just a guess, but maybe something along these lines:

JOIN suapp.AREA_TOPIC.Area_Topic_Code
ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code

Debra TurnerWeb Specialist IIAuthor Commented: 2016-11-30

Dear Stefan,
Thank you for your quick response. I am getting new errors with these changes.

Invalid object name 'suapp.AREA_TOPIC.Area_Topic_Code'

And I'm not sure what you mean by:
"btw, please use table alias names and qualify all used column names with it"
Thank you for your help.
debra

Debra TurnerWeb Specialist IIAuthor Commented: 2016-11-30

Dear Ray,

I changed my query to reflect your example and am now getting a new error.
Error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'suapp.AREA_TOPIC.Area_Topic_Code'.

SELECT First_Name, Last_Name, Department_Name, suapp.FACULTY.Faculty_UUID, Faculty_Email, Faculty_Title, Faculty_Profile_Link FROM suapp.FACULTY
JOIN (
SELECT DISTINCT
suapp.RESEARCH_MATCH.Faculty_UUID
FROM suapp.RESEARCH_MATCH
JOIN suapp.AREA_TOPIC.Area_Topic_Code
ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
WHERE suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
AND suapp.AREA_TOPIC.Area_Topic_Code = 'CD-a'
) rm ON suapp.FACULTY.Faculty_UUID = rm.Faculty_UUID ORDER BY suapp.FACULTY.Last_Name, suapp.FACULTY.First_Name;

With your suggestion, the above is my new query that is getting the error (inside the sql console)
Thank you for your help.
debra

LVL 38

ste5anSenior DeveloperCommented: 2016-11-30

Yup, there is another problem. You "joined" a column instead of a table.

This should do it:

SELECT  F.First_Name ,
        F.Last_Name ,
        F.Department_Name ,
        F.Faculty_UUID ,
        F.Faculty_Email ,
        F.Faculty_Title ,
        F.Faculty_Profile_Link
FROM    suapp.FACULTY F
        INNER JOIN ( SELECT DISTINCT
                            RMI.Faculty_UUID
                     FROM   suapp.RESEARCH_MATCH RMI
                            INNER JOIN suapp.AREA_TOPIC [AT] ON RMI.Area_Topic_Code = [AT].Area_Topic_Code
                                                                AND [AT].Area_Topic_Code = 'CD-a'
                   ) RM ON F.Faculty_UUID = RM.Faculty_UUID
ORDER BY F.Last_Name ,
        F.First_Name;

Open in new window

btw, an EXIST is in this case sometimes better (no DISTINCT necessary):

SELECT  F.First_Name ,
        F.Last_Name ,
        F.Department_Name ,
        F.Faculty_UUID ,
        F.Faculty_Email ,
        F.Faculty_Title ,
        F.Faculty_Profile_Link
FROM    suapp.FACULTY F
WHERE   EXISTS ( SELECT *
                 FROM   suapp.RESEARCH_MATCH RMI
                        INNER JOIN suapp.AREA_TOPIC [AT] ON RMI.Area_Topic_Code = [AT].Area_Topic_Code
                                                            AND [AT].Area_Topic_Code = 'CD-a'
                                                            AND F.Faculty_UUID = RM.Faculty_UUID )
ORDER BY F.Last_Name ,
        F.First_Name;