Click here to Skip to main content
14,694,397 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
The following stored procedure works fine but I need to add another condition in relation to another table called tblItems. I require tblAccounts.accountID to join with tblItems.AccountID so I can check if tblItems.fileID > 0 for every accountID with @bootsaledate and if so, then return results for the code below..

SELECT *,
CASE WHEN RowNo < cnt THEN 'N' ELSE 'Y' END AS lastbox
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY dateAdded desc) as [RowNo],
COUNT(*) OVER () cnt,
tblAccounts.skypeUserName, tblAccounts.contactName, tblorders.friendlyOrderID
FROM tblOrders
INNER JOIN tblAccounts ON tblOrders.accountID=tblAccounts.accountID
WHERE bootSaleDate = @bootSaleDate AND orderStatus='Completed'
) t
WHERE RowNo BETWEEN (@page*8)-7 AND (@page*8)


What I have tried:

I've tried the following but it looks to return a row result for every occurance of fileID in tblItems..

SELECT *,
CASE WHEN RowNo < cnt THEN 'N' ELSE 'Y' END AS lastbox
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY orderDate desc) as [RowNo],
COUNT(*) OVER () cnt,
tblAccounts.skypeUserName, tblAccounts.contactName, tblorders.friendlyOrderID
FROM tblOrders
INNER JOIN tblAccounts ON tblOrders.accountID=tblAccounts.accountID
INNER JOIN tblItems ON tblOrders.accountID=tblItems.accountID
WHERE EXISTS(select 1 from tblItems WHERE tblItems.fileID > 0 AND tblItems.accountID = tblOrders.accountID AND tblOrders.bootSaleDate = @bootSaleDate)  AND tblOrders.bootSaleDate = @bootSaleDate AND tblOrders.orderStatus='Completed' 
) t
WHERE RowNo BETWEEN (@page*8)-7 AND (@page*8)
Posted
Updated 30-Apr-20 2:40am
v2

This is a comment and suggestion rather than a solution

A WHERE EXISTS is not the best way for you here, it is overly complex for what you need. As a result it will be detrimental to the performance of the query. Also I note you include the code
AND tblOrders.bootSaleDate = @bootSaleDate
which pretty much does nothing. That suggests you've let it drift from where it should be. I did test your code and I don't get what you are seeing but your data may include factors my test does not. EXISTS returns a TRUE as soon as it finds something but that includes a NULL record (as opposed to no-records) so if your data causes a null to be returned by the sub-query then that might explain it. Whatever I am speculating I wouldn't waste time finding out what was wrong instead I'd recommend you continue first by removing the unnecessary complexity, and there is a much simpler method immediately available.

Remove the WHERE EXISTS completely and just add an AND to the ON clause of the tblItems inner join so
SELECT *,
CASE WHEN RowNo < cnt THEN 'N' ELSE 'Y' END AS lastbox
FROM
(
  SELECT ROW_NUMBER() OVER (ORDER BY orderDate desc) as [RowNo],
  COUNT(*) OVER () cnt,
  tblAccounts.skypeUserName, tblAccounts.contactName, tblorders.friendlyOrderID
  FROM tblOrders
  INNER JOIN tblAccounts 
  ON tblOrders.accountID=tblAccounts.accountID
  INNER JOIN tblItems 
  ON tblOrders.accountID=tblItems.accountID AND tblItems.fileID > 0
  WHERE 
    tblOrders.bootSaleDate = @bootSaleDate 
    AND tblOrders.orderStatus='Completed' 
) t
WHERE RowNo BETWEEN (@page*8)-7 AND (@page*8)


Much cleaner.
   
Comments
Member 13779417 29-Apr-20 10:52am
   
Thanks for the reply. However, i get the same result with your code.. Multiple rows of the same accountID, skypeUserName, contactName, friendlyOrderID. It looks like it selects a row for every occurance of fileID existing in tblitems for that accountid and date. What I need is it just to select the information once for each accountID no matter how may occurances of fileID exist for that date, just as long as one exists.
ah ok I understand what you are after now. Wood for the trees problem. Don't need to do much to make your code work at all.

I still don't much like using EXISTS but it might be appropriate here and will work if you remove this:
INNER JOIN tblItems ON tblOrders.accountID=tblItems.accountID 
You don't use it for anything, except for giving you multiple rows instead of just 1 (thats supposed to be funny -- sorry). The ON Clause on the JOIN is echoed by the WHERE clause in your EXISTS.

What your original was doing was giving you all records which had any of those records having a fileID > 0. Which might be useful at some point in the future but I think the code below will work for you.

SELECT *,
CASE WHEN RowNo < cnt THEN 'N' ELSE 'Y' END AS lastbox
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY orderDate desc) as [RowNo],
COUNT(*) OVER () cnt,
tblAccounts.skypeUserName, tblAccounts.contactName, tblorders.friendlyOrderID
FROM tblOrders
INNER JOIN tblAccounts ON tblOrders.accountID=tblAccounts.accountID
WHERE EXISTS(select 1 from tblItems WHERE tblItems.fileID > 0 AND tblItems.accountID = tblOrders.accountID)  
AND 
tblOrders.bootSaleDate = @bootSaleDate AND tblOrders.orderStatus='Completed' 
) t
WHERE RowNo BETWEEN (@page*8)-7 AND (@page*8)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900