Click here to Skip to main content
15,039,149 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table in which data shows like:-

ID  -  LevelID -    LevelValue              -  ParentID
   18  -    2     -    External sensor label   -  1,5
   24  -    2     -    Left side               -  1
   49  -    3     -    Bent bottom of door     -  31
   50  -    3     -    Cracked                 -  12,22


ParentID column datatype is nvarchar(). now i need a select query to get only those column in which parentID is 1.

expected result is :-

ID  -  LevelID -    LevelValue              -  ParentID
   18  -    2     -    External sensor label   -  1,5
   24  -    2     -    Left side               -  1



could anyone suggest me the solution.

What I have tried:

i use like keyword but not getting expected result.

select * from Table  where  parentid like '%1%'
Posted
Updated 21-Jan-21 23:26pm

1 solution

Don't do it like that: storing IDs as CSV in a SQL column is a bad idea as it is stupidly difficult to modify when the parent gets changed.
Instead add a second table with three columns (an IDENTITY Id row, and two foreign key rows to the parent and child IDs). You can then retrieve what you need using a JOIN, and the system will ensure that you don't get "orphaned" child nodes as well as making changes to the parent / child relationship a whole lot easier!
   
Comments
TCS54321 22-Jan-21 5:29am
   
tnx for your suggestion.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900