Solved

SQL Query - relational

Posted on 2011-03-14
3
380 Views
Last Modified: 2012-05-11
I have a table PairIDs with columns FirstID and SecondID
FirstID            SecondID
120      6098
121                  9192


I have another table MainID with columns MainID, MainPairID
MainID           MainPairID
800812            120
800992            121
900782            6098
900324             9192


I have another table CompletionID with columns MainID, CompletionNumber
MainID   CompletionNumber
800812                3
800992                4
900782                5
900324                6


I need a query to display
PairID.FirstID, PairID.SecondID, MainID(for PairID.FirstID), MainID(for PairID.SecondID), CompletionNumber( for PairID.FirstID), CompletionNumber(for PairID.SecondID)

As Follows:
FirstID SecondID MainID      Main_ID      CompletionNumber      CompNo
120    6098              800812      900782         3                                        5
121    9192              800992      900324         4                6


JOINS:
MainID inner join CompletionID on MainID.MainID = CompletionID.MainID

PairID inner join MainID on PairID.FirstID

PairID inner join MainID on PairID.SecondID
0
Comment
Question by:spirose
[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
  • 2
3 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35131337
Try the Attached.  Note: I used left outer joins in places because I don't know if the data may or may not exist.
SELECT P.FirstID
  ,P.SecondID
  ,P1.MainID
  ,P2.MainID
  ,P1.CompletionID
  ,P2.CompletionID
FROM PairIDs P
LEFT OUTER JOIN
    (SELECT P.FirstID
      ,M.MainID
      ,C.CompletionNumber
    FROM PairIDs P
    INNER JOIN MainID M
    ON  M.MainPairID = P.SecondID
    LEFT OUTER JOIN CompletionID C
    ON  M.MainID = C.MainID
    ) P1
ON  P.FirstID = P1.FirstID
LEFT OUTER JOIN
    (SELECT P.SecondID
      ,M.MainID
      ,C.CompletionNumber
    FROM PairIDs P
    INNER JOIN MainID M
    ON  M.MainPairID = P.SecondID
    LEFT OUTER JOIN CompletionID C
    ON  M.MainID = C.MainID
    ) P2
ON  P.SecondID = P2.SecondID;

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35131349
try this query.
select p.FirstID,p.SecondID,m1.MainID,m2.MainID Main_ID,c1.CompletionNumber,c2.CompletionNumber CompNo
  from PairIDs p
  join MainIDs m1 on p.FirstID = m1.MainPairID
  join MainIDs m2 on p.SecondID = m2.MainPairID
  join CompletionID c1 on m1.MainID = c1.MainID
  join CompletionID c2 on m2.MainID = c2.MainID

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35131359
you can use LEFT JOIN if data is not available in other tables.
select p.FirstID,p.SecondID,m1.MainID,m2.MainID Main_ID,c1.CompletionNumber,c2.CompletionNumber CompNo
  from PairIDs p
  left join MainIDs m1 on p.FirstID = m1.MainPairID
  left join MainIDs m2 on p.SecondID = m2.MainPairID
  left join CompletionID c1 on m1.MainID = c1.MainID
  left join CompletionID c2 on m2.MainID = c2.MainID

Open in new window

0

Featured Post

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

635 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