• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

SQL - Need Help on the below Query

Hi  All,

Need help on Query on below requirement.

I have 2 tables.

Parent

ID     NAME      ParentID
1A     AAA           P1
2A     BBB           P2
3A     CCC            P3
P1      DDD         Null
P2      EEE           NUll  
P3      FFF           NUll


Child
classID    ChildID
cls1           1A
cls1           2A
cls1           3A

Parent's ID column and Child's Child column are in primary key -foreign key relation ship.

My requirement is

Need to get Parents names for the children - Name(parent Table) based on  the childID (Child Table).

I have written query as below but not getting  results

select  P.Name from
parent p  where P.ID = (
select parentID from parent where
P.ID=(Select c.ChildID  from child c where c.classid = 'cls1' ))

Giving error as  "Multi part identifier could not be bound"  - since with class id "cls1" there
are many childID present in the child table

How do i write this query ??. Do i need to use cursor  and get childID one-by-one.
O/p should be as

ChildID  ChildNAme  ParentName
!A             AAA               DDD
2A            BBB               EEE
3A            CCC                FFF


Thanks
0
B_Pati
Asked:
B_Pati
  • 2
1 Solution
 
Jared_SCommented:
SELECT c.childid, c.name as ChildName, p.name as ParentName
FROM
Parent P, Child C
WHERE p.ID = c.ChildID
and c.classid = 'cls1'
0
 
Jared_SCommented:
I made some assumptions about the names of the child name column. You may need to change that.

You won't need a cursor to get this done.
0
 
ThomasianCommented:
You need to join table Parent twice
SELECT C.ChildID, P1.NAME as ChildName, P2.NAME as ParentName
FROM
	Child C INNER JOIN
	Parent P1 ON C.ChildID=P1.ID LEFT JOIN
	Parent P2 ON P1.ParentID=P2.ID

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now