Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query - relational

Posted on 2011-03-14
3
Medium Priority
?
390 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
  • 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 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

963 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