Please help! Select query in SQL

Question

0.00/5 (No votes)

See more:

ID	Name	   Designation	Level	        userid
15	Sh. B.M. Babbar	SDE-R2	00010104	       ELE15
16	Sh. Surinder  	AE-R2	0001010401	        ELE16
17	Sh. Roop Lal	JE-R2	000101040101	ELE17
18	Sh. Mansatinder	JE-R3	000101040102	ELE18
328	testnews	    JE_R2	000101040103	JE_R2
329	testnews	   JE_R2	000101040104	JE_R2
330	testnew12	   AE-R3	000101040105	AE-R3
331	testnews	   AE-R3    000101040106	AE-R3

This my employeeList table and above following table result showing sde(SDE-R2) as parent under AE as child and AE(AE-R2) under JE as subchild desiganation. i want to get only sde under AE Data through by level

i try this query "Select * from EmployeeList where level like '00010104%'" but showing all records

What I have tried:

i want to show only child data by parent data (SDE-R2 00010104)
"

16	Sh. Surinder  	AE-R2	0001010401	        ELE16

"

Posted 2-Dec-18 22:11pm

Nishant.Chauhan80

Updated 3-Dec-18 0:04am

v2

Add a Solution

Comments

CHill60 3-Dec-18 4:42am

Your data structure is not clear - how does "Level" indicate parent or child data?

Nishant.Chauhan80 3-Dec-18 5:00am

level indicate hierarchy
sde parent
AE child
JE subchild

i want to show only child data through by parent level

CHill60 3-Dec-18 5:06am

So include that in your where clause ... and Designation like 'AE%'

Jörgen Andersson 3-Dec-18 6:14am

It's an enumerated path. Which is one of the three major ways to store hierarchical data. Same as MS is using in the hierarchyid

OriginalGriff 3-Dec-18 4:48am

That makes no sense at all. Your query will - rightly - show all records becuas ethat is what you told it to do.
Exactly what you want it to show however is not at all clear - I have no idea what "level" is, where "AE Data" is stored, or what on earth the relationships are supposed to be.

Start by showing us exactly what output you want, and explain why the other rows are not to be included.

Nishant.Chauhan80 3-Dec-18 4:59am

i want to show only child data by parent data (SDE-R2 00010104)

"16 Sh. Surinder AE-R2 0001010401 ELE16"

OriginalGriff 3-Dec-18 5:11am

Yes, but ... we have no idea how you define "parent" and "child" - and it is not at all obvious from your data. What defines it as a "parent" and hat says "this is a child of ..."?
Normally, you'd have an "ID" column, and a "ParentID" column which links back to it (often via a Foreign Key relationship). But I don't see any of that here.

Jörgen Andersson 3-Dec-18 6:09am

It's an enumerated path, as opposed to an adjacency list or nested Set

OriginalGriff 3-Dec-18 6:21am

Which is a very poor idea ... :sigh:

Jörgen Andersson 3-Dec-18 6:58am

Tell microsoft :-)
https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-2017

They all have their uses. At my previous job we ended up using both enumerated paths and adjacency lists, for performance reasons.
A pretty good overview here: https://www.cnblogs.com/ttltry-air/archive/2012/08/10/2633164.html

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Slacker007 · Answer 1 · 2018-12-02T23:14:00

i want to show only child data by parent data (SDE-R2 00010104)

psuedo sql query is:

Select * from <<ChildTable>>
Inner join <<ParentTable>>
on <childtable.FKID>> = <<ParentTable.PKID>>
Where <<ParentTable.<<level>> like '00010104%'

I think it important to mention that you should never use Select * in a production query, as that can have negative implications for your data path in the future if something were to change with the structure of all the tables involved.

Select * is good for testing and for data dumps, but that is about it, IMHO.

Jörgen Andersson · Answer 2 · 2018-12-03T00:00:00

Solution 2

Since you only want the level below this query should do the job:

SQL

SELECT   *
FROM    EmployeeList
WHERE   LEVEL LIKE '00010104__'

But having a position based hierarchy key is something I'd recommend that you rethink.

And yes, never use SELECT * in production code.

Posted 3-Dec-18 0:00am

Jörgen Andersson

CHill60 · Answer 3 · 2018-12-03T00:05:00

I think solution 1 got the wrong end of the stick. Based on your comment you just need to add and AND clause to your query (and as pointed out by Slacker007, correct your column list). E.g.

SQL

Select ID, Name, Designation, Level, userid
from EmployeeList where level like '00010104%'
and Designation like 'AE%'

If you also want to include the "sub-child" designation you mention then add an OR clause but you will need to enclose it in brackets e.g.

SQL

Select ID, Name, Designation, Level, userid
from EmployeeList where level like '00010104%'
and ( Designation like 'AE%' or Designation like 'JE%' )

Please help! Select query in SQL

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0