CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
nima
nima

Reputation: 1283

SQL sub query with complex criteria

I have a table like this:

TransId.   LayerNo.  AccountId.   
100.       1.        2.        
100.       2.        3.        
120.       1.        5.        
120.       2.        6.    
120.       3.        12.     
70.        1.        2.

I want to find transId(s) where: (LayerNo = 1 and (accountId = 2 or 5)) and (LayerNo = 2 and (accountId = 3 or 6))

And result set would be row no 1,2,3,4. How could I write query to get the result? My database is SQL server 2008 r2 Thanks in advance Nima

Upvotes: 2

Views: 525

Answers (7)

Ef_R
Ef_R

Reputation: 1

This intersection is empty. If you take the values where LayerNo = 1 and LayerNo = 2 and intersect them their intersection is empty because these events are mutually exclusive. I believe this error comes from how the question was originally stated. I might be wrong but the predicate should have been (LayerNo = 1 and (accountId = 2 or 5)) OR (LayerNo = 2 and (accountId = 3 or 6)) Replace the AND with an OR. If the predicate was stated correctly then the intersect is correct but will always be empty.

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52675

One approach is to ensure that each transID must have two records that satisfy the conditions you outlined.

SELECT * FROM 
TABLE
WHERE TransID IN( 
    SELECT TransId 
    FROM   table 
    WHERE  ( layerno = 1 
             AND  accountid IN ( 2, 5 )  ) 
            OR ( layerno = 2 
                 AND  accountid IN( 3, 6 )  ) 
    GROUP BY 
        TransId
    HAVING Count(*) = 2
    )

However this could be a problem if you can have multple records where layerno = 1. So you can use self joins instead to ensure the criteria.

SELECT DISTINCT a.transid 
FROM   table a 
       INNER JOIN table b 
         ON a.transid = b.transid 
       INNER JOIN table c 
         ON a.transid = c.transid 
WHERE  b.layerno = 1 
       AND accountid IN ( 2, 5 ) 
       AND c.layerno = 2 
       AND accountid IN ( 3, 6 )

That said Martin's INTERSECT approach is probably the best

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

SELECT 
   base.TransId,  
   base.LayerNo,  
   base.AccountId
FROM TableX AS base
  JOIN TableX AS a
    ON  a.TransId = base.TransId
    AND a.LayerNo = 1 AND a.AccountId IN (2, 5)
  JOIN TableX AS b
    ON  b.TransId = base.TransId
    AND b.LayerNo = 2 AND b.AccountId IN (3, 7)
WHERE (base.LayerNo = 1 AND base.AccountId IN (2, 5)) 
   OR (base.LayerNo = 2 AND base.AccountId IN (3, 7))

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453920

SELECT TransId
FROM   your_table
WHERE  ( layerno = 1
         AND accountid IN ( 2, 5 ) )
INTERSECT
SELECT TransId
FROM   your_table
WHERE  ( layerno = 2
         AND accountid IN ( 3, 6 ) )

Upvotes: 5

AJP
AJP

Reputation: 2125

create table #temp

( rowId Int Identity(1,1), transId int)

INSERT INTO #temp(transId)  
select TransId
from TableName
where (layerNo = 1 and accountID IN (2, 5))
OR (layerNo = 2 and accountId IN (3, 6))
select * from #temp

Upvotes: 0

Mithrandir
Mithrandir

Reputation: 25397

Do you mean:

SELECT 
   TransId,  
   LayerNo,  
   AccountId
FROM Table
WHERE (LayerNo = 1 AND AccountId IN (2, 5)) OR 
      (LayerNo = 2 AND AccountId IN (3, 7))

Upvotes: 0

Akhil
Akhil

Reputation: 7610

SELECT  *
  FROM  table
 WHERE  (LayerNo = 1 AND (AccountID = 2 OR AccountID = 5))
   OR   (LayerNo = 2 AND (AccountID = 3 OR AccountID = 6))

Upvotes: -1

Related Questions