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,
C.CrewID,
C.CrewName,
C.TerminationDate,
C.Status,
E.CrewCategoryDesc AS RosterGroup,
F.PositionCode
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