Question: I have 3 tables in my sql database like these :

```
Documents : (DocID, FileName) //list of all docs that were attached to items
Items : (ItemID, ...) //list of all items
DocumentRelation : (DocID, ItemID) //the relation between docs and items

```

In my winform application I have showed all records of **Items** table in a grid view and let user to select several rows of it and then if he press EditAll button another grid view should fill by file name of documents that are related to these selected items but not all of them,

> 
> **Just each of documents which have relation with *ALL* selected items**
> 
> 
> 

Is there any query (*sql or linq*) to select these documents?

Answer: 
Try something like:

```
string query;
foreach (Item in SelectedItems)
{
   query += "select DocID from DocumentRelation where ItemID =" + Item.Id;
   query += "INTERSECT";
}
query -= "INTERSECT";

```

And exec the Query;
Here's one approach. I'll let you figure out how you want to supply the list of items as arguments. And I also assume that (DocID, ItemID) is a primary key in the relations table. The `having` condition is what enforces your requirement that all select items are related to the list of documents you're seeking.

```
;with ItemsSelected as (
    select i.ItemID
    from Items as i
    where i.ItemID in (<list of selected ItemIDs>)
)
select dr.DocID
from DocumentRelation as dr
where dr.ItemID in (select ItemID from ItemsSelected)
group by dr.DocID
having count(dr.ItemID) = (select count(*) from ItemsSelected);

```

**EDIT**

As far as I can tell, the accepted answer is equivalent to the solution here despite OP's comment below.

I did some quick tests with a very long series of `intersect` queries and confirmed that you can indeed expect that approach to become gradually slower with an increasing number of selected items. But a much worse problem was the time taken just to compile the queries. I tried this on a very fast server and found that that step took about eight seconds when roughly one hundred intersects were concatenated.

SQL Fiddle didn't let me do anywhere near as many before producing this error (and taking more than ten seconds in the process): *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.*

There are several possible methods of passing a list of arguments to SQL Server. Assuming that you prefer the dynamic query solution I'd argue that this version is still better while also noting that there is a SQL Server limit on the number of values inside the `in`.

There are plenty of ways to have this stuff blow up.