SQL several characteristics query Microsoft SQL Server-CodePudding

I have a problem with my SQL query. I have operations with (specific ID for each one) and I have participants in those operations that can be (seller, facilitator, manager, assistant)

Table looks like:

ID    Volume   Participant
---------------------------
122   100      Sellers
122   100      Facilitator
123    50      Sellers
123    50      Manager
123    50      Facilitator
124   120      Sellers
124   120      Assistant
125   180      Manager
125   180      Sellers
125   180      Facilitator

I want to extract operations where, for example, seller and manager have participated. In this case, the seller and manager have participated in operations 123 and 125

SELECT ops.opsId, ops.opsvolume, tranche.participant
FROM ops 
INNER JOIN tranche ON ops.opsID = tranche.opsId
WHERE tranche.participant = 'seller' 
  AND tranche.participant = 'manager'

But obviously the participants can not be two roles at the same time, it is the operation that has several roles, any suggestions?

CodePudding user response：

There's not enough information in the question yet to fully answer it. But perhaps you can start with this:

SELECT * FROM ops WHERE ID IN (
    SELECT ID
    FROM ops
    WHERE participant IN ('sellers', 'manager')
    GROUP BY ID
    HAVING COUNT(*) = 2
)

CodePudding user response：

I would start by pivoting out new columns, after which you can simply use a WHERE clause

with q as
(
  SELECT ops.opsId, 
       max(ops.opsvolume) Volume, 
       max(case when tranche.Participant = 'seller' then 1 else 0 end) HasSeller,
       max(case when tranche.Participant = 'manager' then 1 else 0 end) HasManager
  FROM ops 
  INNER JOIN tranche ON ops.opsID = tranche.opsId
  GROUP BY ops.opsId
)
select opsId, Volume
from q
where HasSeller = 1 
 and  HasManager = 1