Adding a condition using EXISTS with a second INNER JOIN

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

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 29-Apr-20 0:39am

Member 13779417

Updated 30-Apr-20 1:40am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Eek Ten Bears · Answer 1 · 2020-04-29T04:07:00

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.

Eek Ten Bears · Answer 2 · 2020-04-30T01:41:00

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)

Adding a condition using EXISTS with a second INNER JOIN

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0