expression of non-boolean type

问题:

Using this statement:

select * from qvalues where rowid ,name,compound in (
    select rowid,name,compound from qvalues where rowid in (select rowid from batchinfo where instrument='tf1')
    group by rowid,name,compound
    having COUNT(*)>1 
    )
    group by rowid,name,compound
having rid=min(rid)

This error occurs:

Msg 4145, Level 15, State 1, Line 3 An expression of non-boolean type specified in a context where a condition is expected, near ','. Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'group'.

What is wrong with this SQL statement? I need to find all the occurrences of those three fields in the table having min(rid).

Update Using this query, the outer select is not working. What am I doing wrong?

select * from qvalues where rid not in (
select q.rowid, q.name, q.compound, min(q.rid)
    from qvalues q
        inner join batchinfo b
            on q.rowid = b.rowid
                and b.instrument = 'tf1'
    group by q.rowid, q.name, q.compound
    having count(*) > 1)

回答1:

I think this is equivalent to what you're trying to achieve.

select min(q.rid)
    from qvalues q
        inner join batchinfo b
            on q.rowid = b.rowid
                and b.instrument = 'tf1'
    group by q.rowid, q.name, q.compound
    having count(*) > 1

回答2:

The final having min(rid) needs to have comparison

Saying that, there is no associated GROUP BY which you can see if you rearrange your code snippet

select * from qvalues
where rowid ,name,compound in (
               select rowid,name,compound from qvalues
               where rowid in (select rowid from batchinfo where instrument='tf1')
               group by rowid,name,compound
               having COUNT(*)>1 
               )
--missing group by
having min(rid) -- > foo

Edit: reformulated:

select
   *
from
    (
    select
       rowid,name,compound, min(q.rid) as minrid
    from
       qvalues q
    where
       EXISTS (SELECT * FROM batchinfo b where b.instrument='tf1' AND b.rowid = q.rowid)
    group by
        rowid,name,compound
    having
        COUNT(*)>1
    ) foo
    JOIN
    qvalues q2 On foo.rowid = q.rowid AND foo.name = q.name AND foo.compound = q.compound
                   AND foo.minrid = q2.rid

And your original error probably comes from a lack of alias of the derived table, coupled with the fact that SQL Server does not support multi-column IN. I use EXISTS not JOIN because you may get more rows from qvalues JOIN batchinfo than you expect (based on Joe's answer)