CODEWITHSUNDEEP
sqlsql-server
Aaron
Aaron

Reputation: 29

Creating INNER Joins with IIF

I am trying to join three tables using and inner join and have the contents of one change colour if an e-mail has been sent.

Below is my query

SELECT IIF(COUNT Holdsent.job)>0, #STD, #RED) AS Colour, jobs.job, jobs.jobstatus, jobs.client, jobs.logdate
FROM jobs INNER JOIN clients ON clients.client = jobs.client INNER JOIN holdsent ON holdsent.job = jobs.job
WHERE (jobs.jobstatus = 'HOLD' OR jobs.jobstatus = 'CLIHOLD')

Below is the error I receive

Expected lexical element not found: (missing ( in aggregate function [Parsing Expression (column1 in the SELECT clause)] -- Location of error in the SQL statement is:1 SELECT IIF(COUNT Holdsent.job)>0,#STD, #RED) AS COLOUR,jobs.job, jobs.jobstatus,jobs.client,jobs.logdate FROM jobs INNER JOIN clients ON clients.client = jobs.client INNER JOIN holdsent ON holdsent.job = jobs.job WHERE (jobs.jobstatus = 'HOLD' OR jobs.jobstatus = 'CLIHOLD')

I am new to SQL and can do basic queries, but am not clear on IIF. Thank you in advance for any help you can provide.

Upvotes: 0

Views: 1728

Answers (2)

tommy_o
tommy_o

Reputation: 3783

Sorry, I didnt mock up test tables for this, but I think you want to subquery against your holdsent table to get the count value into the main query, then use three parameters in your IIF() function. I didnt make a test case, but I think this will suit your needs:

SELECT      IIF((isnull(holdsentCount.jobCount,0)>0, #STD, #RED) AS Colour
            , jobs.job
            , jobs.jobstatus
            , jobs.client
            , jobs.logdate
FROM        jobs 
JOIN        clients 
ON          clients.client = jobs.client 
LEFT JOIN   (select     holdsent.job
                        , count(*) as jobCount 
            from        holdsent 
            group by    holdsent.job ) as holdsentCount
ON          holdsentCount.job = jobs.job
WHERE       (jobs.jobstatus = 'HOLD' OR jobs.jobstatus = 'CLIHOLD')

Also, wrap the holdsentCount.jobCount with isnull() so that it returns 0 if it isn't returned in the subquery.

EDIT: I dont know what '#STD' or '#RED' are so I left them as-is.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269453

iif is a special function from MS Access, which SQL Server has started supporting with the most recent version. The correct SQL form is the case statement. An improved version of your query, written in standard SQL, is:

SELECT (case when COUNT(hs.job)>0 then #STD else #RED end) AS Colour,
       j.job, j.jobstatus, j.client, j.logdate
FROM jobs j INNER JOIN
     clients c
     ON c.client = j.client INNER JOIN
     holdsent hs
     ON hs.job = j.job
WHERE j.jobstatus in ('HOLD', 'CLIHOLD')

Upvotes: 3

Related Questions