<div>
&quot; I dont need any results from the added table I just need to include in the where statement a where itemid = 'XX'.&quot; 
 
if the query you have gives you the correct results just put it between brackets and alias it to make it a sub-query then just SELECT from it where itemid = 'XX'. 
 
should be something like: 
 
SELECT * FROM 
( 
...your query here 
) &nbsp;Q 
where q.itemid = 'XX'
</div>

<div>
I don't always have an item I want to filter on. The client might it might not use an item filter. 
 
To clarify the before works correctly without the items.
</div>

<div>
Hmmm....then you have a few choices- either prepare the statement in whatever UI code and run it the way you want it or better...create a SQL stored procedure where you pass these in as parameters where they will be NULL if &quot;The client might it might not use an item filter. &quot;&nbsp;and build inside the SP the code you want to run 
 
 
<a href="http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures" rel="ugc">http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures</a> 
 
Would it be possible to post the query in plain text instead of the PNG?
</div>

<div>
Here is the query... 
 

<pre><code class="code-10 nolinks" id="code-20-40436177-1">SELECT vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, SUM(HstvbofqCheck.CheckTotal) AS tSpent, COUNT(HstvbofqAssignment.HstvbofqAssignmentID) AS tVisits, 
 vbogcData.Balance
FROM vbofqMemberAccount INNER JOIN
 vboProfile ON vbofqMemberAccount.FKvboProfileID = vboProfile.vboProfileID INNER JOIN
 vboProfileContact ON vboProfile.vboProfileID = vboProfileContact.FKvboProfileID INNER JOIN
 vboProfileLocation ON vboProfile.vboProfileID = vboProfileLocation.FKvboProfileID INNER JOIN
 vbofqLastVisit ON vbofqMemberAccount.vbofqMemberAccountID = vbofqLastVisit.FKvbofqMemberAccountID INNER JOIN
 HstvbofqAssignment ON vbofqMemberAccount.vbofqMemberAccountID = HstvbofqAssignment.FKvbofqMemberAccountID INNER JOIN
 HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID INNER JOIN
 HstvbofqCheckItem ON HstvbofqCheck.HstvbofqCheckID = HstvbofqCheckItem.FKHstvbofqCheckID LEFT OUTER JOIN
 vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum
WHERE (vbofqMemberAccount.CardNumber LIKE '%29834000015891%') AND (vboProfile.LastName LIKE '%%') AND (HstvbofqAssignment.Status = 4)
GROUP BY vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, vbogcData.Balance
ORDER BY vbofqMemberAccount.CardNumber
</code></pre>
Also I am completely self taught in SQL so all this is very new to me. &nbsp;Thanks for the help!
</div>

before.png

after.png

<div>
That previous post of the Query is the not working correctly one that I need help with. &nbsp;Here is the query working correctly without the ITEMS portion added in... 
 

<pre><code class="code-10 nolinks" id="code-20-40436182-1">SELECT vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, SUM(HstvbofqCheck.CheckTotal) AS tSpent, COUNT(HstvbofqAssignment.HstvbofqAssignmentID) AS tVisits, 
 vbogcData.Balance
FROM vbofqMemberAccount INNER JOIN
 vboProfile ON vbofqMemberAccount.FKvboProfileID = vboProfile.vboProfileID INNER JOIN
 vboProfileContact ON vboProfile.vboProfileID = vboProfileContact.FKvboProfileID INNER JOIN
 vboProfileLocation ON vboProfile.vboProfileID = vboProfileLocation.FKvboProfileID INNER JOIN
 vbofqLastVisit ON vbofqMemberAccount.vbofqMemberAccountID = vbofqLastVisit.FKvbofqMemberAccountID INNER JOIN
 HstvbofqAssignment ON vbofqMemberAccount.vbofqMemberAccountID = HstvbofqAssignment.FKvbofqMemberAccountID INNER JOIN
 HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID LEFT OUTER JOIN
 vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum
WHERE (vbofqMemberAccount.CardNumber LIKE '%29834000015891%') AND (vboProfile.LastName LIKE '%%') AND (HstvbofqAssignment.Status = 4)
GROUP BY vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, vbogcData.Balance
ORDER BY vbofqMemberAccount.CardNumber
</code></pre>
</div>

<div>
I forgot to mention and sorry about it...next time - anytime you post online please delete or replace any confidential info like CardNumber...etc. I removed it and would be good if you could do that as well...never post real numbers details for protection please.
</div>

<div>
This is not live data and these numbers do not reflect the live working numbers but mental note has been made. &nbsp; Thanks for your continued efforts in helping me with this!
</div>

<div>
Is this what you mean and need? Please note that I moved the 
INNER JOIN vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum 
right after vbofqMemberAccount which makes sense and should not alter your record set plus now you have the join to the Item table and ItemId = 'XX' right in the INNER JOIN 
... 
HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID INNER JOIN 
&nbsp; &nbsp; &nbsp; HstvbofqCheckItem ON HstvbofqCheck.HstvbofqCheckID = HstvbofqCheckItem.FKHstvbofqCheckID AND HstvbofqCheckItem.ItemID = 'XX' 
.... 
 
 
So overall the code should look like: 
 
 
SELECT &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vbofqMemberAccount.CardNumber, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vbofqLastVisit.DateOfBusiness, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM(HstvbofqCheck.CheckTotal) AS tSpent, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNT(HstvbofqAssignment.HstvbofqAssignmentID) AS tVisits, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vbogcData.Balance 
&nbsp;FROM vbofqMemberAccount INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum INNER JOIN 
&nbsp; &nbsp; &nbsp; vboProfile ON vbofqMemberAccount.FKvboProfileID = vboProfile.vboProfileID INNER JOIN 
&nbsp; &nbsp; &nbsp; vboProfileContact ON vboProfile.vboProfileID = vboProfileContact.FKvboProfileID INNER JOIN 
&nbsp; &nbsp; &nbsp; vboProfileLocation ON vboProfile.vboProfileID = vboProfileLocation.FKvboProfileID INNER JOIN 
&nbsp; &nbsp; &nbsp; vbofqLastVisit ON vbofqMemberAccount.vbofqMemberAccountID = vbofqLastVisit.FKvbofqMemberAccountID INNER JOIN 
&nbsp; &nbsp; &nbsp; HstvbofqAssignment ON vbofqMemberAccount.vbofqMemberAccountID = HstvbofqAssignment.FKvbofqMemberAccountID INNER JOIN 
&nbsp; &nbsp; &nbsp; HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID INNER JOIN 
&nbsp; &nbsp; &nbsp; HstvbofqCheckItem ON HstvbofqCheck.HstvbofqCheckID = HstvbofqCheckItem.FKHstvbofqCheckID AND HstvbofqCheckItem.ItemID = 'XX' 
&nbsp;WHERE (vbofqMemberAccount.CardNumber LIKE '%xxxxxxxx%') AND (vboProfile.LastName LIKE '%%') AND (HstvbofqAssignment.Status = 4) 
&nbsp;GROUP BY vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, vbogcData.Balance 
&nbsp;ORDER BY vbofqMemberAccount.CardNumber
</div>

<div>
No I dont think that is right. &nbsp;I just need the table HstvbofqCheckItem joined in and then in my where clause I will be passing the ItemID = '' there if needed. &nbsp;If the table HstvbofqCheckItem can be added in and it not &quot;multiply&quot; my count and sum I should be good to go. &nbsp;Am I making any sense at all?
</div>

<div>
Here is a more stripped down example of the same &quot;problem&quot; 
 
SELECT &nbsp; &nbsp; eManager.eTitle, eManager.eFrom, eManager.eSubject, COUNT(eLogs.eLogID) AS tSends, COUNT(eLogDetails.eLogDetailID) AS tMails 
FROM &nbsp; &nbsp; &nbsp; &nbsp; eManager INNER JOIN 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; eLogs ON eManager.eMgrID = eLogs.eMgrID INNER JOIN 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; eLogDetails ON eLogs.eLogID = eLogDetails.eLogID 
GROUP BY eManager.eTitle, eManager.eFrom, eManager.eSubject 
 
In this example there is one record in the eManager table. &nbsp;In the eLogs table there is one record linked back to eManager. If I leave out the eLogDetails my tSends shows 1 as it should. &nbsp;In eLogDetails there are two records linked back to eLogs. &nbsp;When I join in the eLogDetails table my tSends shows 2 so does tMails. &nbsp; 
 
Is this a little easier to follow the problem? &nbsp;I am trying to get in this example tSends as 1 but tMails as 2.
</div>

<div>
CREATE PROCEDURE [GetSalesOrders] (@ItemID SYSNAME) 
AS 
SET NOCOUNT ON; 
 
DECLARE @SQL NVARCHAR(4000); 
 
IF @ItemID IS NOT NULL 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET @SQL = N' 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT * FROM 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;( 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT &nbsp; &nbsp; vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, SUM(HstvbofqCheck.CheckTotal) AS tSpent, COUNT(HstvbofqAssignment.HstvbofqAssignmentID) AS tVisits, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vbogcData.Balance, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HstvbofqCheck.HstvbofqCheckID 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM &nbsp; &nbsp; &nbsp; &nbsp; vbofqMemberAccount INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vboProfile ON vbofqMemberAccount.FKvboProfileID = vboProfile.vboProfileID INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vboProfileContact ON vboProfile.vboProfileID = vboProfileContact.FKvboProfileID INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vboProfileLocation ON vboProfile.vboProfileID = vboProfileLocation.FKvboProfileID INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vbofqLastVisit ON vbofqMemberAccount.vbofqMemberAccountID = vbofqLastVisit.FKvbofqMemberAccountID INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HstvbofqAssignment ON vbofqMemberAccount.vbofqMemberAccountID = HstvbofqAssignment.FKvbofqMemberAccountID INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID LEFT OUTER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE &nbsp; &nbsp; (vbofqMemberAccount.CardNumber LIKE ''%29834000015891%'') AND (vboProfile.LastName LIKE ''%%'') AND (HstvbofqAssignment.Status = 4) 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GROUP BY vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, vbogcData.Balance 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) Q WHERE EXISTS 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(SELECT ItemId FROM HstvbofqCheckItem 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;WHERE HstvbofqCheck.HstvbofqCheckID = HstvbofqCheckItem.FKHstvbofqCheckID 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND HstvbofqCheckItem.ItemId = '+@ItemId+' 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ORDER BY vbofqMemberAccount.CardNumber'; 
 
--if NO value or NULL value is passed in then run the original select 
IF @ItemID IS NULL 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET @SQL = N' 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT &nbsp; &nbsp; vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, SUM(HstvbofqCheck.CheckTotal) AS tSpent, COUNT(HstvbofqAssignment.HstvbofqAssignmentID) AS tVisits, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vbogcData.Balance, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HstvbofqCheck.HstvbofqCheckID 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM &nbsp; &nbsp; &nbsp; &nbsp; vbofqMemberAccount INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vboProfile ON vbofqMemberAccount.FKvboProfileID = vboProfile.vboProfileID INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vboProfileContact ON vboProfile.vboProfileID = vboProfileContact.FKvboProfileID INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vboProfileLocation ON vboProfile.vboProfileID = vboProfileLocation.FKvboProfileID INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vbofqLastVisit ON vbofqMemberAccount.vbofqMemberAccountID = vbofqLastVisit.FKvbofqMemberAccountID INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HstvbofqAssignment ON vbofqMemberAccount.vbofqMemberAccountID = HstvbofqAssignment.FKvbofqMemberAccountID INNER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID LEFT OUTER JOIN 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE &nbsp; &nbsp; (vbofqMemberAccount.CardNumber LIKE ''%29834000015891%'') AND (vboProfile.LastName LIKE ''%%'') AND (HstvbofqAssignment.Status = 4) 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GROUP BY vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, vbogcData.Balance 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ORDER BY vbofqMemberAccount.CardNumber'; 
 
EXEC sp_executeSQL @SQL 
 
GO 
 
--to test both cases please execute the stored proc with NULL for no value or some ItemId 
EXEC [GetSalesOrders] 112345 
--OR like below if ItemId is a string 
EXEC [GetSalesOrders] 'A1B2C3D4' 
 
--then no ItemId 
EXEC [GetSalesOrders] NULL
</div>

<div>
Thank you very much. &nbsp;This works exactly as I needed. &nbsp;A+ solution!
</div>

<div>
<div class="content wysiwyg-content">
Please refer to attached images for better explanation. &nbsp;This is very difficult to articulate in words. &nbsp; 
 
Back story: &nbsp;This query is for a restaurant loyalty system and tracking card members visits/ticket details. &nbsp; 
 
I have the query seen in before.png that is correctly joining all the tables and giving the information needed. &nbsp;There is a count and a sum to show the total number of visits and amount spent. &nbsp;The client wants to be able to limit the data to specific items sold within the tickets. &nbsp;When I add in the table that contains the items sold per ticket it throws the numbers way off because the sum and count are treating the added table as a multiplier for each item sold see after.png. &nbsp;I dont need any results from the added table I just need to include in the where statement a where itemid = 'XX'. 
 
Any help greatly appreciated! &nbsp;Please let me know if you need anymore information. 
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882457/before.png" target="_blank" class="file-inline" title="before.png (34 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>before.png</a> 
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882458/after.png" target="_blank" class="file-inline" title="after.png (39 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>after.png</a>
</div>
</div>

Please refer to attached images for better explanation.  This is very difficult to articulate in words.  

Back story:  This query is for a restaurant loyalty system and tracking card members visits/ticket details.  

I have the query seen in before.png that is correctly joining all the tables and giving the information needed.  There is a count and a sum to show the total number of visits and amount spent.  The client wants to be able to limit the data to specific items sold within the tickets.  When I add in the table that contains the items sold per ticket it throws the numbers way off because the sum and count are treating the added table as a multiplier for each item sold see after.png.  I dont need any results from the added table I just need to include in the where statement a where itemid = 'XX'.

Any help greatly appreciated!  Please let me know if you need anymore information.

MSSQL Query Join not Grouping

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.