SQL - Need Help on the below Query

Posted on 2012-09-17
Last Modified: 2012-09-17
Hi  All,

Need help on Query on below requirement.

I have 2 tables.


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

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

Question by:B_Pati
    LVL 12

    Expert Comment

    SELECT c.childid, as ChildName, as ParentName
    Parent P, Child C
    WHERE p.ID = c.ChildID
    and c.classid = 'cls1'
    LVL 12

    Expert Comment

    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.
    LVL 22

    Accepted Solution

    You need to join table Parent twice
    SELECT C.ChildID, P1.NAME as ChildName, P2.NAME as ParentName
    	Child C INNER JOIN
    	Parent P1 ON C.ChildID=P1.ID LEFT JOIN
    	Parent P2 ON P1.ParentID=P2.ID

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now