Hi Folks -
I'm trying to create a SQL query (or whatever will work) but having some trouble getting the expected results do the complexity. Allow me to explain. I have attached an excel workbook which contains my two tables and then a tab which shows my expected results.
I need to join [rdAssets] on [rdInvestments] using the [AssetID] column which is obviously available in both tables. However, I want to return the following columns only if [PartnershipAlias] is the same across the PFI codes:
Code:
[rdAssets].[AssetID],
[rdAssets].[AssetAlias],
[rdInvestments].[PartnershipID],
[rdInvestments].[PartnershipAlias]
in the [rdInvestments] table I have highlighed in green the records in green that meet this criteria while the records in red that don't. Please let me know if you need any additional information.
Here is my SQL query but need to add that additional layer of complexity but not sure how?
Code:
SELECT
[rdAssets].[AssetID],
[rdAssets].[AssetAlias],
[rdInvestments].[PartnershipID],
[rdInvestments].[PartnershipAlias]
FROM [rdAssets]
INNER JOIN [rdInvestments] ON [rdAssets].[AssetID] = [rdInvestments].[AssetID]
WHERE
([rdAssets].[PartnershipID] IS NULL AND
[rdInvestments].[PartnershipID] IS NOT NULL)