Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to join 3 tables. Expand / Collapse
Author
Message
Posted Friday, November 15, 2013 2:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 15, 2013 4:37 AM
Points: 1, Visits: 4
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 !!
Post #1514631
Posted Friday, November 15, 2013 7:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 13,111, Visits: 11,946
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1514724
Posted Friday, November 15, 2013 7:50 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:42 PM
Points: 8,567, Visits: 9,071
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
Post #1514885
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse