CODEWITHSUNDEEP
c#sqlsql-server
user2633804
user2633804

Reputation: 257

SQL Query Intersections

I have a problem using Intersections in SQL:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

I'm using a for loop to generate all queries to intersect and retrieve the desired data.

My problem:

I need to select varC where exists a varA in a list of values AND varB also in a list of values.

Using WHERE IN varA ('','','','',...'') and varB ('','','','',...'') the result are not correct because it not restrict all values.

So I'm using INTERSECT:

for (int i = 0; i < varA.Count; i++)
{
    for (int j = 0; j < varB.Count; j++)
    {
        sqlQuery += @"SELECT DISTINCT varC FROM tblData WHERE varD='XPTO' AND varA='" + varA[i] + "' AND varB='" + varB[j] + "' INTERSECT ";
    }
}   

   sqlQuery = sqlQuery.Remove(sqlQuery.Length - 10, 10); //to remove last INTERSECT
   sqlQuery += " ORDER BY varC ASC";

There is any idea how to do it?

It works using a small dataset but if we increase the list of values it crashes even using a high command timeout.

Thanks in advance

Upvotes: 0

Views: 195

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Perhaps you want this:

select varc
from tbldata
group by varc
having sum(case when varA in (<list of A values>) then 1 else 0 end) > 0 and
       sum(case when varB in (<list of B values>) then 1 else 0 end) > 0;

Upvotes: 2

sarh
sarh

Reputation: 6627

You can generate a script with temporary table having these three arg columns like this:

declare @t table (A nvarchar(max), B nvarchar(max), D nvarchar(max))
insert into @t (A, B, D) values ('valA1', 'valB1', 'valD1')
insert into @t (A, B, D) values ('valA2', 'valB2', 'valD2')
...

select distinct varC 
from tblData D 
inner join @t T on D.A = T.A and D.B = T.B and D.D = T.D

Upvotes: 1

Ben
Ben

Reputation: 35613

I suspect you want to generate a query of this form:

SELECT DISTINCT varC 
FROM tblData 
WHERE 
    varD='XPTO' 
    AND (
        (varA = "VALUEA1" and varB = "VALUEB1")
     OR (varA = "VALUEA2" and varB = "VALUEB2")
     OR (varA = "VALUEA3" and varB = "VALUEB3")
     OR (varA = "VALUEA4" and varB = "VALUEB4")
    )

Upvotes: 2

Related Questions