Need SQL Query for this scenario

Question

Table structure:

ProjectID ParentID  Name        Path
   38          2    client      |2|38|
  108         38    Sample1     |2|38|108
  205         38    Sample2     |2|38|205
  206         38    Sample3     |2|38|206
  207         38    Sample4     |2|38|207

Query:

SELECT Name, ProjectId
  FROM dbo.Projects project
 WHERE ParentID = 38

This would give me all the rows from the above table except the first row.

However I want to achieve the following!

ProjectID   ParentID    Name           Path
   108         38        clientSample1  |2|38|108
   205         38        clientSample2  |2|38|205
   206         38        clientSample3  |2|38|206
   207         38        clientSample4  |2|38|207

SQL experts please let me know the query on how to achieve this. I appreciate your input.

PN: Sorry about the table structure. I couldn't paste any images yet.

Answer 1

The first row has a ParentID = 2, your query explicitly ask for ParentID = 38

try:

SELECT Name, ProjectId FROM dbo.Projects project WHERE (ParentID = 38 OR ProjectId = 38)

Answer 2

If I understand correctly, and you simply want 'client' in front of the name:

SELECT P1.ProjectId, P1.ParentId, P2.[Name] + P1.[Name], P1.Path
FROM Projects P1
INNER JOIN Projects P2 ON P1.ParentId = P2.ProjectId
Where P1.ParentId = 38

Effectively, you're joining the Projects table onto itself a second time, to find out the parent's name. From there, you can concatenate the two names together.