Filter Query Results

Hi,

I have an interface that allows the user to filter the data to be returned.

e.g. if the user selects @SeniorManager then the query should return all data related to this.

So if the user selects @SeniorManager and @Auditor then the query should return all data matching both @SeniorManager AND @Auditor.

FundID and ClientID are the ID's that link each table.

Am I correct in using UNION ALL below?? thanks.





Code Block
SELECT SeniorMgr, null AS Auditor, null AS Legal
FROM FundClient f
INNER JOIN FundPortfolio p
ON p.ClientID = f.ClientID
INNER JOIN Staff s
ON (s.ClientID = p.ClientID) AND (s.FundID = p.PortfolioID) AND (s.ClientID = f.ClientID)
WHERE s.ClientID = @ClientID AND s.FundID = @FundID
AND (@SeniorManager = null Or SeniorMgr = @SeniorManager)
UNION ALL

SELECT null AS SeniorMgr, fa.Auditor, null AS Legal
FROM FundClient f
INNER JOIN FundPortfolio p
ON p.ClientID = f.ClientID
INNER JOIN (Auditor a
INNER JOIN FundAuditor fa
ON fa.AuditorID = a.Auditor)
ON (a.ClientID = p.ClientID) AND (a.FundID = p.PortfolioID) AND (a.ClientID = f.ClientID)
WHERE a.Auditor = @Auditor
AND a.ClientID = @ClientID AND a.FundID = @FundID

UNION ALL

SELECT null AS SeniorMgr, null AS Auditor, fl.Legal
FROM FundClient f
INNER JOIN FundPortfolio p
ON p.ClientID = f.ClientID
INNER JOIN (Legal l
INNER JOIN LegalCounsel fl
ON fl.LegalID = l.Legal)
ON (l.ClientID = p.ClientID) AND (l.FundID = p.PortfolioID) AND (l.ClientID = f.ClientID)
WHERE l.LegalC = @Legal
AND l.ClientID = @ClientID AND l.FundID = @FundID