Avoid repeating consistent conditions on where clause-CodePudding

So I'm writing a console application in C# that extracts a whole bunch of data and I am using this query to get the majority of it:

        private static string HistoryQuery(Guid itemDetailId) {
           var sql = $@"
Select IH.ItemDetailID, i.Type, v.Number, v.Title, V.MajorRevisionNumber AS RevisionNumber,
Ih.Action, Ih.ActionedBy, Ih.ActionedDate, Ih.Comment, iwft.Type as TaskType, iwft.StartDate,
iwft.CompletedDate, Iwft.Status, Iwft.Outcome, Iwfta.ActionedBy as TaskActionedBy, Iwfta.ActionDate, Iwfta.Action as TaskAction, Iwfta.Comment as TaskComment, lid.URL
From ItemView v
Join ItemHistory Ih
    On Ih.ItemDetailID = v.ItemDetailId
Join Item i
    On i.ItemID = v.ItemId
Left Outer join ItemWorkflowTask Iwft
    On Iwft.ItemDetailID = v.ItemDetailId
Left outer Join ItemWorkflowTaskAction Iwfta
    On Iwfta.ItemWorkflowTaskID = Iwft.ItemWorkflowTaskID
Left outer Join DocumentItemDetail did 
    ON did.ItemDetailID = Ih.ItemDetailID
Left outer Join ItemDetail id 
    ON id.ItemID = did.LinkItemID
Left outer JOIN LinkItemDetail lid
    ON lid.ItemDetailID = id.ItemDetailID
Where I.Type='Document' AND IH.ItemDetailID = '{itemDetailId}'
AND Ih.Action NOT IN ('Access', 'AddToFavourites', 'ItemDefaultFavouriteChanged', 'RemoveFromFavourites', 'View') OR
 I.Type ='ProcessMap' AND IH.ItemDetailID = '{itemDetailId}'
AND Ih.Action NOT IN ('Access', 'AddToFavourites', 'ItemDefaultFavouriteChanged', 'RemoveFromFavourites', 'View')";

            return sql;
        }

Now this does work and gets me what I need but I am just wondering, is there a way of rewriting my where clause:

Where I.Type='Document' AND IH.ItemDetailID = '{itemDetailId}'
AND Ih.Action NOT IN ('Access', 'AddToFavourites', 'ItemDefaultFavouriteChanged', 'RemoveFromFavourites', 'View') OR
 I.Type ='ProcessMap' AND IH.ItemDetailID = '{itemDetailId}'
AND Ih.Action NOT IN ('Access', 'AddToFavourites', 'ItemDefaultFavouriteChanged', 'RemoveFromFavourites', 'View')

So that I am not repeating consistent conditions? as the only difference is that I.Type can be either 'Document' or 'ProcessMap'.

CodePudding user response：

You just need to use and AND after what you don't want to repeat i.e.:

Where Ih.Action NOT IN ('Access', 'AddToFavourites', 'ItemDefaultFavouriteChanged', 'RemoveFromFavourites', 'View')
    AND 
        (I.Type='Document' AND IH.ItemDetailID = '{itemDetailId}'
        OR I.Type ='ProcessMap' AND IH.ItemDetailID = '{itemDetailId}')

CodePudding user response：

Thanks to the comment from canton7 and the answer from YungDeiza, I've changed it to:

Where IH.ItemDetailID = '{itemDetailId}'
AND Ih.Action NOT IN ('Access', 'AddToFavourites', 'ItemDefaultFavouriteChanged', 'RemoveFromFavourites', 'View')
AND (I.Type = 'Document' OR I.Type = 'ProcessMap')

Which works how I'm needing it to