Displaying nested parent and child records

Question

For example having a parent record with ID being 1, you display all child records having the ID 1 and you the next records link to displaying a parent ID being 2 with it's child records. I'm developing an application with Asp classic with sql server. i want to know if there's a query that can give me that results Another example is just like facebook when you click on a picture you get the various comments on it and when you click on the next arrow, get it turns to a new pictures also with its comments.

SELECT  *
FROM dbo.users_pics INNER JOIN profile ON users_pics.email = profile.email Left  Join photo_comment On users_pics.u_pic_id = photo_comment.pic_id
WHERE users_pics.wardrobe = MMColParam

meaning after a pictues shows, you can use a next picture link to show the upcoming picture with its comments

Your question sounds broad and unclear. Of course it is possible to write queries like that (you already got one), but it's hard to see what exactly 'that' is in this case. So please start writing and ask a more specific question if you get stuck. — GolezTrol, May 27 '14 at 13:19
The query you have should give you that data. SQL is not meant to "nest" data - that's the responsibility of the UI layer (ASP in your case). — D Stanley, May 27 '14 at 13:19
If you really want recursively nested data, you can have a look at this: http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server But my guess is, that in your case you won't need it. There seems to be no real infinite number of paren-child relationship, just a bunch of comments belonging to a picture, which is a classic inner join approach. Since comments and other pictures are unrelated, just make two queries. One to get the pictures, one to get the comments for the 'active' picture. — GolezTrol, May 27 '14 at 13:21
I think I see what you're doing, and perhaps the "nested" query isn't what you need at all. It might be better to do a page refresh or an AJAX call to get new data when the user clicks on "Next," and in that case you will only need to retrieve one parent ID at a time anyway. — catfood, May 27 '14 at 13:24
The way you phrase this question suggests a level of expertise that may not be sufficient (yet) for the project you're attempting. Have you considered doing a *much* simpler application first? Also, why ASP Classic? That makes everything so much more difficult. — catfood, May 27 '14 at 13:27

score 0 · Answer 1 · edited May 27 '14 at 15:05

Do you want to create hierarchical relationship like as "employees and their manager" along with levels, If yes you can easily implement using recursion through common table expression -

enter image description here

To apply recursion on this use the below query -

WITH EMPLOYEECTE(EMPID,NAME,MANAGERID,[LEVEL])
AS
(
SELECT EMPLOYEEID,FIRSTNAME + ' ' + LASTNAME AS [NAME],MANAGERID,1  
FROM DBO.MYEMPLOYEES
WHERE MANAGERID IS NULL
UNION ALL
SELECT ME.EmployeeID,ME.FirstName + ' ' + ME.LastName AS [NAME],ME.ManagerID,EMPCTE.LEVEL+1 AS [LEVEL]
FROM DBO.MYEMPLOYEES ME
INNER JOIN EMPLOYEECTE EMPCTE ON ME.ManagerID=EMPCTE.EMPID
)
SELECT * FROM EMPLOYEECTE

Displaying nested parent and child records

1 Answers1