CODEWITHSUNDEEP
sqlsql-servert-sql
Anjali
Anjali

Reputation: 163

Why am I getting syntax error in SQL Server 'in' clause?

I have written a query in SQL Server and am getting an error.

Code:

with cte as (
    select pe1.name as P1,
        pe2.name as P2,
        m.title as T
    from participant pa1
    join participant pa2 on pa2.idMeeting = pa1.idMeeting
        and pa2.idPerson > pa1.idPerson
    join person pe1 on pe1.id = pa1.idPerson
    join person pe2 on pe2.id = pa2.idPerson
    join meeting m on m.id = pa1.idMeeting
),
cte_meet_max as (
    select count(*) cnt, P1,P2
    from cte
    group by P1,P2    
)
select *
from cte 
where (P1,P2) in (
    select P1,P2 
    from cte_meet_max 
    where cnt = (select max(cnt) from cte_meet_max)
);

Error:

Msg 4145, Level 15, State 1, Line 25
An expression of non-boolean type specified in a context where a condition is expected, near ','.

Desired result:

desired result

Can we put two columns with 'in' clause in SQL Server?

Please help.

Thanks in advance.

Note: I tried solving the question posted before question

Upvotes: 3

Views: 353

Answers (3)

A.Bahrami
A.Bahrami

Reputation: 162

You should only concatenate P1 and P2 in you're where clause

with cte as (
        select pe1.name as P1,
            pe2.name as P2,
            m.title as T
        from participant pa1
        join participant pa2 on pa2.idMeeting = pa1.idMeeting
            and pa2.idPerson > pa1.idPerson
        join person pe1 on pe1.id = pa1.idPerson
        join person pe2 on pe2.id = pa2.idPerson
        join meeting m on m.id = pa1.idMeeting
    ),
    cte_meet_max as (
        select count(*) cnt, P1,P2
        from cte
        group by P1,P2    
    ),
    meet_max as (select P1,P2
        from cte_meet_max 
        where cnt = (select max(cnt) from cte_meet_max))
    select *
    from cte ,meet_max
    where cte.P1=meet_max.P1 and cte.P2=meet_max.P2
        
    );

Upvotes: 0

Jon Armstrong
Jon Armstrong

Reputation: 4694

Optional solution:

I used detail from both questions here.

Thanks to @Sander for the initial test case. I've made a tiny change to insert the missing meeting (id = 500), in case someone wanted to join with that table, and I've created a fiddle to show the test case:

Working Test Case for SQL Server

The solution counts the meetings each pair of participants have in common.

Additionally, I use a window function to, in the same query expression, find the MAX count over the entire set of results, avoiding one separate step.

As a result, the last query expression only needs to compare the current cnt to the max_cnt, available in each row, without needing correlated behavior.

Most are assuming the person names are unique. I also made that assumption in the first query, but used the primary keys (or would have been PKs, if constraints were created) in the second query.

WITH cte AS (
        SELECT pe1.name as p1
             , pe2.name as p2
             , COUNT(*) AS cnt
             , MAX(COUNT(*)) OVER () AS max_cnt
          FROM participant pa1
          JOIN participant pa2
            ON pa2.idMeeting = pa1.idMeeting
           AND pa2.idPerson  > pa1.idPerson
          JOIN person pe1 on pe1.id = pa1.idPerson
          JOIN person pe2 on pe2.id = pa2.idPerson
         GROUP BY pe1.name, pe2.name
     )
SELECT *
  FROM cte
 WHERE cnt = max_cnt
;

If we don't need person names, we can avoid a little work:

WITH cte AS (
        SELECT pa1.idPerson as p1
             , pa2.idPerson as p2
             , COUNT(*) AS cnt
             , MAX(COUNT(*)) OVER () AS max_cnt
          FROM participant pa1
          JOIN participant pa2
            ON pa2.idMeeting = pa1.idMeeting
           AND pa2.idPerson  > pa1.idPerson
         GROUP BY pa1.idPerson, pa2.idPerson
     )
SELECT *
  FROM cte
 WHERE cnt = max_cnt
;

Upvotes: 1

user330315
user330315

Reputation:

An EXISTS condition is the proper way to rewrite this in SQL Server:

with cte as (
    select pe1.name as P1,
        pe2.name as P2,
        m.title as T
    from participant pa1
    join participant pa2 on pa2.idMeeting = pa1.idMeeting
        and pa2.idPerson > pa1.idPerson
    join person pe1 on pe1.id = pa1.idPerson
    join person pe2 on pe2.id = pa2.idPerson
    join meeting m on m.id = pa1.idMeeting
),
cte_meet_max as (
    select count(*) cnt, P1,P2
    from cte
    group by P1,P2    
)
select *
from cte 
where exists (
    select *
    from cte_meet_max m1
    where m1.cnt = (select max(m2.cnt) from cte_meet_max m2)
      and m1.p1 = cte.p1
      and m1.p2 = cte.p2
)

Upvotes: 4

Related Questions