What is the difference between the two sql statements?
good afternoon
Everything we are trying to do is inside the trigger, make sure the user does not insert two boards that have "one" in the name. These fees must be handled individually.
For some reason, it looks like the top of the sql quit running two weeks ago. To get around this, I rewrote it in the second way and got the correct results. I'm confused why the first part seemed to work for the last few years and now it doesn't?
SELECT @AloneRecordCount = count(*)
FROM inserted i
INNER JOIN deleted d on i.id = d.id
WHERE i.StatusID = 32
AND d.StatusID <> 32
AND i.id IN
(SELECT settlementid FROM vwFundingDisbursement fd
WHERE fd.DisbTypeName LIKE '%Alone'
AND fd.PaymentMethodID = 0)
SELECT @AloneRecordCount = count(i.id)
FROM inserted i INNER JOIN
deleted d on i.id = d.id
JOIN vwFundingDisbursement fd on i.id = fd.settlementid
WHERE i.StatusID = 32
AND d.StatusID <> 32
AND fd.DisbTypeName like '%Alone'
AND fd.PaymentMethodID = 0
this is
no error on SQL Server 2005 , instead the top statement will only return 1 or zero while the bottom statement will return the actual number found.
a source to share
A schematic (or at least how the view is created) would help, but here's a guess ...
If you are looking for multiple rows in vwFundingDisbursement with the value "Alone" in the name of the distribution type, then the JOIN will return multiple rows because your original table (INSERTED) is concatenated into multiple rows in the view. If you use IN, even though SQL doesn't care if it returns multiple matches, it will only give you one row.
As an example:
CREATE TABLE dbo.Test_In_vs_Join1
(
my_id INT NOT NULL
)
CREATE TABLE dbo.Test_In_vs_Join2
(
my_id INT NOT NULL
)
INSERT INTO dbo.Test_In_vs_Join1 (my_id) VALUES (1)
INSERT INTO dbo.Test_In_vs_Join1 (my_id) VALUES (2)
INSERT INTO dbo.Test_In_vs_Join1 (my_id) VALUES (3)
INSERT INTO dbo.Test_In_vs_Join1 (my_id) VALUES (4)
INSERT INTO dbo.Test_In_vs_Join1 (my_id) VALUES (5)
INSERT INTO dbo.Test_In_vs_Join2 (my_id) VALUES (1)
INSERT INTO dbo.Test_In_vs_Join2 (my_id) VALUES (1)
INSERT INTO dbo.Test_In_vs_Join2 (my_id) VALUES (2)
INSERT INTO dbo.Test_In_vs_Join2 (my_id) VALUES (3)
INSERT INTO dbo.Test_In_vs_Join2 (my_id) VALUES (3)
SELECT
T1.my_id,
COUNT(*)
FROM
dbo.Test_In_vs_Join1 T1
INNER JOIN dbo.Test_In_vs_Join2 T2 ON
T2.my_id = T1.my_id
GROUP BY
T1.my_id
SELECT
T1.my_id,
COUNT(*)
FROM
dbo.Test_In_vs_Join1 T1
WHERE
T1.my_id IN (SELECT T2.my_id FROM dbo.Test_In_vs_Join2 T2)
GROUP BY
T1.my_id
On the other hand, burying a column inside another column like this is a violation of the normalized form and just requires problems. Executing this kind of business logic in a trigger is also a dangerous path once you learn.
a source to share
Quantity (*)
count (i.id)
will return different values based on NULL values
In SQL, what is the difference between count (*) and count ('x')?
a source to share