SQLTeam.com | Weblogs | Forums

Help with a Two Facts to a Dim


My two tables I gave an aliases of A & B are fact tables and the table I gave the name C to is a Dim table. I am trying to pull all fields that A and B where they match with C...in some places I will have an A but no B or a B but no A. For some reason the way I wrote this is not working out any help I would be grateful for. I am using MSSQL

SELECT A.DutyName AS Actual,
B.DutyName AS Published,
A.RosterDate AS ActualRosDate,
B.RosterDate AS PubRosDate,
E.CrewCategoryDesc AS RosterGroup,

FROM FctCrewAvailabilityDetails AS A,
FctCrewAvailabilityDetailsPublished AS B,
Full Outer Join DimCrewMaster AS C
ON A.CrewMasterKey = C.CrewMasterKey = B.CrewMasterKey,
INNER JOIN AIMS.FactCrewQualification_Primary AS D
ON C.CrewMasterKey = D.CrewMasterKey,
INNER JOIN DimCrewCategory AS E
ON D.CrewCategoryKey = E.CrewCategoryKey,
INNER JOIN DimCrewPositions AS F
ON D.CrewPositionKey = F.CrewPositionKey

WHERE A.DutyName IN ('TVL', 'NDL', 'TRAI', 'HUET', 'OPC', 'CRM','LPC', 'SIM', 'GRND', 'X', 'RIOX', 'MEAX', 'CFBX', 'VIXX', 'SBFX', 'MEDV', 'NSBY', 'O')
AND C.TerminationDate = '1980-01-01 00:00:00:000'
AND E.CrewCategoryDesc IN ('Brazil Crew')
AND F.PositionCode IN ('FO', 'CP')

ORDER BY C.CrewID, A.RosterDate


Sounds to me like you want to left outer join a and b table.
Try this:

select a.DutyName as Actual
      ,b.DutyName as Published
      ,a.RosterDate as ActualRosDate
      ,b.RosterDate as PubRosDate
      ,e.CrewCategoryDesc as RosterGroup
  from DimCrewMaster as c
       left outer join FctCrewAvailabilityDetails as a
                    on a.CrewMasterKey=c.CrewMasterKey
                   and a.DutyName in ('TVL'
       left outer join FctCrewAvailabilityDetailsPublished as b
                    on b.CrewMasterKey=c.CrewMasterKey
       inner join AIMS.FactCrewQualification_Primary as d
               on c.CrewMasterKey=c.CrewMasterKey
       inner join DimCrewCategory as e
               on e.CrewCategoryKey=d.CrewCategoryKey
              and e.CrewCategoryDesc='Brazil Crew'
       inner join DimCrewPositions as f
               on f.CrewPositionKey=d.CrewPositionKey
              and f.PositionCode in ('FO','CP')
 where c.TerminationDate='1980-01-01 00:00:00:000'
 order by c.CrewID


Thanks you for the Reply. That helped me get all my data into one extract.

Thanks again I appreciate your time