Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

join

table PX
CID, projectEndDate, memberid, providerid, POs
1, 2011-06-30 23:59:00, 12345, 09876, IP
2, 2012-06-30 25:59:00, 12345, 09876, IP
3, 2011-06-30 24:59:00, 23456, 98765, OP
4, 2011-06-30 23:59:00, 23456, 98765, OP
5, 2011-06-30 23:59:00, 34567, 87654, IP
6, 2011-06-30 23:59:00, 45678, 76543, OP

Table MasterChild contains CID from table PX as master child associations.
MasterCID, ChildCID
2,1
3,4

How do i join the above two tables to show projectEndDate for both MasterCID and ChildCID as follows:
MasterCID, MasterProjectStartDate, ChildCID, ChildProjectStartDate
2, 2012-06-30 25:59:00, 1, 2011-06-30 23:59:00
3, 2011-06-30 24:59:00, 4, 2011-06-30 23:59:00

Thank you.


0
patd1
Asked:
patd1
  • 3
  • 3
2 Solutions
 
tim_csCommented:

SELECT
  A.MasterCid
  B.ProjectEndDate
  ,A.ChildCID
  ,C.ProjectEndDate
FROM
   MasterChild A
      INNER JOIN PX B 
         ON A.MasterCID = B.CID
      INNER JOIN PX C
          ON A.ChildCid = C.CID

Open in new window

0
 
tim_csCommented:
Missed a comma.
SELECT 
  A.MasterCid 
  ,B.ProjectEndDate 
  ,A.ChildCID 
  ,C.ProjectEndDate 
FROM 
   MasterChild A 
      INNER JOIN PX B  
         ON A.MasterCID = B.CID 
      INNER JOIN PX C 
          ON A.ChildCid = C.CID

Open in new window

0
 
Haris DjulicCommented:
select f.MasterCID, e.projectEndDate as projectstartdate, f.ChildCID , g.projectEndDate as enddate       
from (select cid, projectEndDate from PX) as  e
 left join MasterChild  f on e.cid=f.MasterCID
left join (select cid, projectEndDate from PX) as  g on g.cid=f.ChildCID
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Haris DjulicCommented:
removed the Null joins...


select f.MasterCID, e.projectEndDate as projectstartdate, f.ChildCID , g.projectEndDate as enddate	 
from (select cid, projectEndDate from PX) as  e inner join MasterChild  f on e.cid=f.MasterCID
inner join (select cid, projectEndDate from PX) as  g on g.cid=f.ChildCID

Open in new window

0
 
tim_csCommented:
Did mine not work?
0
 
patd1Author Commented:
oops! I meant to accept multiple solutions, but clicked on the other button by mistake. I tested both and they bot worked. In fact I found your solution to be simpler to understand. I am sorry. I don't know if I can change it to mark both solutions as accepted. Good learning experience. Thanks a ton!
0
 
Haris DjulicCommented:
Is this question going to be closed?
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now