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

MS SQL - How do I fetch the same column twice from one table?


I have two tables. One has a full list of IDs with their descriptions, the other lists the relationship between the IDs. I realise this sounds a little cryptic so I'll try and illustrate it in the code section.

What I need to do is have a full list of IDs with their parent and child descriptions...see desired result table in the coding section.
Table 1:
ID     Descr
1      James
2      Philip
3      Peter
4      Allan
Table 2:
ID     ChildID
1      2
1      3
2      NULL
3      4
4      NULL
Desired Result:
ID    ParentDescr     ChildDescr
1     James               Philip
1     James               Peter
2     Philip                 NULL
3     Peter                 Allan
4     Allan                 NULL

Open in new window

  • 4
  • 3
  • 2
  • +1
1 Solution
Kevin CrossChief Technology OfficerCommented:
Something like this should work:
select p.ID, p.Descr AS ParentDescr, c.Descr AS ChildDescr
from Table1 p
inner join Table2 pc ON pc.ID = p.ID
left join Table1 c ON pc.ChildID = c.ID

Open in new window

joshgeakeAuthor Commented:
thanks, my issue was how to query the same table twice for the descriptions of the child and parent IDs but it looks like this sorts it - i'll comment tomorrow when i'm back in the office.

thanks again!
Select c.ID, C.Desc ParentDescr, m.ChildID, m.Descr as ChildDescr
From Table1 c INNER JOIN (
Select a.ID, a.ChildID, t.Descr
From  Table1 t INNER JOIN  Table2 d
           ON t.ID = ChildID
Where ChildID IS NOT NULL) as m
ON c.ID = m.Id
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

I am really slow today... :-)
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:

Alternatively...Something like this should work...
select ALL.*
select A.id, A.Descr as ParentDescr, C.Descr as ChildDescr from table1 A inner join table2 B on  A.id = B.id inner join table1 C on B.ChildID = C.id where B.ChildID is not null
union all
select A.id, A.Descr, NULL from table1 A inner join table2 B on  A.id = B.id where B.ChildID is null
order by ALL.id

joshgeakeAuthor Commented:
Right i'm still a little stuck, see the code snippet...

It looks like its listing all part groups for every product when what I want it to do is only list its relevent group. Each part can be in a number of product groups but it's usually 1. Thus it will then list the child group.

Any ideas?
SELECT     epar.part AS PartCode, p.prod_group, c.prod_group AS child_group, p.descr AS ParentDescr, c.descr AS ChildDescr
                      wgrp_part ON epar.part = wgrp_part.part INNER JOIN
                      wgrp AS p ON wgrp_part.prod_group = p.prod_group INNER JOIN
                      wgrp_sgrp AS pc ON pc.Prod_group = p.prod_group LEFT OUTER JOIN
                      wgrp AS c ON pc.sub_group = c.prod_group
WHERE     (c.descr IS NOT NULL)
ORDER BY epar.part

Open in new window

joshgeakeAuthor Commented:
by the way...
epar - lists all parts.
wgrp_part - lists all parts and their prod_groups.
wgrp_sgrp - lists all sub_groups and their respective prod_group.
wgrp - lists all prod_groups and sub_groups descriptions.
Sorry, but I am a little confused. Can you please post some sample data from the tables we can work with?
Kevin CrossChief Technology OfficerCommented:
The way (including table structure) the question was asked, it was answered here - http:#24765175.

However, the asker changed things up here - http:#24771302 for which additional information was requested here - http:#24772162 that was never responded to; therefore, would recommend "delete - no refund" unless my original answer will be honored.
I would recommend accepting mvisa's answer since it was answered based on what was requested.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

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