Solved

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

Posted on 2009-07-02
11
588 Views
Last Modified: 2012-08-14
Hi,

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

0
Comment
Question by:joshgeake
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24765175
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

0
 
LVL 2

Author Comment

by:joshgeake
ID: 24765194
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!
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24765212
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
0
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 
LVL 17

Expert Comment

by:pssandhu
ID: 24765226
I am really slow today... :-)
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24765481

Alternatively...Something like this should work...
select ALL.*
from
(
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
) ALL
order by ALL.id

HTH
0
 
LVL 2

Author Comment

by:joshgeake
ID: 24771302
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
FROM         epar LEFT OUTER JOIN
                      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

0
 
LVL 2

Author Comment

by:joshgeake
ID: 24771319
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.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24772162
Sorry, but I am a little confused. Can you please post some sample data from the tables we can work with?
P.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 24975302
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.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 25013765
I would recommend accepting mvisa's answer since it was answered based on what was requested.
P.
0

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 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