SQL Query Intersections

Question

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

Can you edit your question and provide sample data and desired results? Your method is possibly not the best approach. — Gordon Linoff
– Gordon Linoff, Commented Nov 10, 2014 at 14:37
Are you sure you want/need to use the INTERSECT operator? It doesn't feel correct to me since it means it will return distinct values that are returned by both the LEFT and the RIGHT query. Can you indeed give an example of the question you want to ask to the database? I'm sure there is a solution for your requirements that do not require an individual query for each combination of varA and varB. — Maarten
– Maarten, Commented Nov 10, 2014 at 14:45
Hi, this is an example: s11.postimg.org/w61kv1s1f/… If the user select A1 and A2 and B01,B02 I will show only varC that contains selected values in column varA and varB. — user2633804
– user2633804, Commented Nov 10, 2014 at 14:54

Gordon Linoff · Accepted Answer · 2014-11-10 14:36:29Z

2

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")
    )

edited Nov 10, 2014 at 14:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

answered Nov 10, 2014 at 14:35

Ben

35.8k6 gold badges81 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-11-10 14:52:35Z

2

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;

edited Nov 10, 2014 at 14:52

answered Nov 10, 2014 at 14:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

sarh · Accepted Answer · 2014-11-10 14:47:01Z

1

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

edited Nov 10, 2014 at 14:47

answered Nov 10, 2014 at 14:37

sarh

6,7174 gold badges28 silver badges36 bronze badges

Collectives™ on Stack Overflow

SQL Query Intersections

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related