special select query

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 1

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;

Answer 2

Take one string and keep on adding itemid comma separated in that,like 1,2,3 and then write query like

declare ItemID varchar(50);
set ItemID='1,2,3';

select FileName 
from documents 
Left Join DocumentRelation on Documents.DocId = DocumentRelation.DocId 
where 
    DocumentRelation.ItemID in (select * from > dbo.SplitString(ItemID))

and then make one function in database like below

ALTER FUNCTION [dbo].[SplitString] (@OrderList varchar(1000))
RETURNS @ParsedList table (OrderID varchar(1000) ) 
AS BEGIN 
    IF @OrderList = ''  
    BEGIN       
        set @OrderList='Null' 
    end     

    DECLARE @OrderID varchar(1000), @Pos int

    SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','  
    SET @Pos = CHARINDEX(',', @OrderList, 1)

    IF REPLACE(@OrderList, ',', '') <''     
    BEGIN       
        WHILE @Pos 0
        BEGIN
           SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))

           IF @OrderID < '' 
           BEGIN
               INSERT INTO @ParsedList (OrderID) 
               VALUES (CAST(@OrderID AS varchar(1000))) 
               --Use Appropriate conversion             
           END

           SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
           SET @Pos = CHARINDEX(',', @OrderList, 1)
        END     
    END     

    RETURN 
END

Answer 3

Linq

var td =
from s in Items
join r in DocumentRelation on s.ItemID equals r.ItemID
join k in Documents on k.DocID equals r.DocID
where Coll.Contains (s.ItemID)         //Here Coll is the collection of ItemID   which you can store when the users click on the grid view row
select new 
{
 FileName=k.FileName,
 DocumentID= k.DocId
 }; 

You can loop through td collection and bind to your grid view

SQL

create a stored proc to get the relevant documents for the itemID selected from the grid view and paramterize your in clause

 select k.FileName,k.DocId from Items as s inner join 
 DocumentRelation as r on 
 s.ItemID=r.ItemID and r.ItemId in (pass the above coll containing selected ItemIds as an input the SP)
 inner join Documents as k
 on  k.DocId=r.DocIk 

You can get the information on how to parametrize your sql query

Answer 4

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.