Need Help in Search Conditions in Asp.Net from a Database

Transact-SQL
1

Hello All,

Please help me to get the desired output described in attached image.

Thanks in Advance

Regrads:

Muhammad Sami ur Rehman

Sql Search
Sql Search.png1366×768 60 KB

2

This looks like either homework or a self-study exercise. If so, the point is to try it yourself first. if you get stuck, post:

  1. CREATE TABLE statements for the Documents and Reference tables
  2. INSERT INTO statements to populate the tables just created
  3. The query that you are working on, whether it works or not.
3

Hello Sir,

Thank your for the support, And i have tried above mentioned queries and just get at the state where I am able to fetch records in a sorted way like Documents and their References are listed in a ordered way and used joins to get References from Reference Table.

Please suggest me what more should I do ?

Thanks & Regards
Muhammad Sami ur Rehman

4

I have tried RECURSIVE CTE METHOD, Here is the code attached below. The problem is it is not fetching any result. Maybe it stuck in Loop.

WITH DocumentsAndTheirReferences
(DocID,
DocName,
Ref1,
Ref2,
Ref3
)

AS
(
SELECT Reference.DocID,Reference.RefID,Reference.Ref,Reference.Ref,Reference.Ref
FROM Reference
WHERE Reference.DocID = Reference.DocID

UNION ALL

SELECT DocumentsAndTheirReferences.DocID,DocumentsAndTheirReferences.DocName,DocumentsAndTheirReferences.Ref1,DocumentsAndTheirReferences.Ref2,DocumentsAndTheirReferences.Ref3
FROM DocumentsAndTheirReferences
JOIN Reference ON DocumentsAndTheirReferences.DocID = Reference.DocID
)
SELECT * FROM DocumentsAndTheirReferences
ORDER BY DocumentsAndTheirReferences.DocID
OPTION (MAXRECURSION 0)

5

if you post:

then it's easier for us to see what's going on and to experiment with your query. People on the forum will not retype the data from your screenshot