T-SQL stored procedure with joins

Question

I have a problem with a stored procedure. I have 3 tables for a mass mailing service and I want to know how many tasks (table - MMProcessItem) I still need to do...

I have these 3 tables:

Here is my select:

SELECT
    MMAddress.AddressID, MMProcess.ProcessID
FROM 
    MMProcess, MMAddress
LEFT OUTER JOIN
    (SELECT *
     FROM MMProcessItem) Items ON Items.AddressID = MMAddress.AddressID
WHERE 
    Items.ResultID IS NULL
ORDER BY 
    ProcessID, AddressID

And my SQL Code is working fine if there is nothing in MMProcessItem table, this is what I get:

But if I send 1 email, like the one with AddressID = 1 and ProcessID = 1, I don't get anymore the 1 record with AddressID = 1 and ProcessID = 2, I should get a total of 3 records, but what i get is a total of 2 records...

Sorry if this is an amateur mistake, im not used to work with t-sql and do these type of things...

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged. And you should ***most definitely NOT*** mix the two styles!! — marc_s, Oct 10 '16 at 16:38
Worse is mixing them. Usually works but not technically supported by anybody AFAIK. — RBarryYoung, Oct 10 '16 at 16:56

score 3 · Accepted Answer · edited May 23 '17 at 10:30

Your join to MMProcessItem requires two predicates, one to join to MMProcess, and one to join to MMAddress. You are currently only joining to MMAddress. That means that when you add a record with AddressID = 1 and ProcessID = 1 it removes both records where AddressID = 1, not just the one record where AddressID is 1 and ProcessID is 1.

You could rewrite your query as:

SELECT  a.AddressID, p.ProcessID
FROM    MMProcess AS p
        CROSS JOIN MMAddress AS a
        LEFT OUTER JOIN MMProcessItem AS i 
            ON i.AddressID = a.AddressID
            AND i.ProcessID = p.ProcessID
WHERE   i.ResultID IS NULL
ORDER BY p.ProcessID, a.AddressID;

Note the use of explicit join syntax, and also aliases for brevity

Since you are using the LEFT JOIN to MMProcessItem solely for the reason of removing records, then you might find that using NOT EXISTS conveys intention better, but more importantly, it can also perform better.

SELECT  a.AddressID, p.ProcessID
FROM    MMProcess AS p
        CROSS JOIN MMAddress AS a
WHERE   NOT EXISTS
        (   SELECT   1
            FROM    MMProcessItem AS i 
            WHERE   i.AddressID = a.AddressID
            AND     i.ProcessID = p.ProcessID
        )
ORDER BY p.ProcessID, a.AddressID;

T-SQL stored procedure with joins

1 Answers1