November 15, 2013 at 2:17 am
Hi,
I have 2 tables:
Element(Element_Id, Element_Name) and Parent_Child(Parent_Id, Child_Id). Parent_Id and Child Id are actually the element Ids only. An element can have more than 1 parent or more than one child.
Now i want the output like this:
Element_Id Element_Name Parent_Name Child_Name
Plz help !!
November 15, 2013 at 7:13 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 15, 2013 at 7:50 pm
As far as I can tell, what you are asking for is a simple 5-way join: of course in T-SQL that has to written as 4 nested two-way joins - two bottom level inner joins to get names of parent and child of a given element ID, then join one of those to the element table and that result to the Element table again.
To make it easier to read and understand it's probably better to put the two bottom level joins in CTEs and use the old notation for the three way join at the outer level - commas and where clause instead of join operators and on clauses - instead of writing the three way join as two two-way joins.
That gives the following code:
with EP as (select C.Child_Id, E1.Element_Name as Parent_Name
from Element E1 inner join Parent_Child C on C.Parent_Id = E1.Element_id),
EC as (select P.Parent_Id, E2.Element_Name as Child_Name
from Element E2 inner join Parent_Child P on P.Child_Id = E2.Element_Id)
select E.Element_Name, EP.Parent_Name, EC.Child_Name
from Element E, EP, EC
where EP.Child_Id = E.Element_Id and EC.Parent_Id = E.Element_Id;
I guessed at table defintions and invented some data and tested it; but as Sean said, you should really provide DML and test data for a query like this, as otherwise you may find someone has guessed wrong.
Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply