Change IN for EXSITS

Question

Change IN for EXSITS

Edvard Korsbæk

SSCertifiable

Points: 5994
More actions
May 9, 2024 at 7:37 pm
Go to Answer
#4420110

I have had a hard time to understand how to use EXISTS.
I Always use IN instead.
Here it dont work, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Someone who can rewrite this:
SELECT minutes FROM dbo.timeaccountmovement WHERE ownertype = 1 AND timeaccountid = 8 AND ownerid IN
(
SELECT ownerid,timeaccountid FROM dbo.timeaccountmovement WHERE timeaccountid = 73 and ownertype = 1 AND ownerid IN
(
SELECT id FROM dbo.dutyrostershift WHERE shifttype = 1 AND employeeid = 77 AND dato BETWEEN '20230501' AND '20240331'
)
)

Best regards

Edvard Korsbæk
ScottPletcher

SSC Guru

Points: 101095
More actions
May 9, 2024 at 7:41 pm
Answer
#4420111

Remove the second column from the first IN subquery:

SELECT minutes FROM dbo.timeaccountmovement WHERE ownertype = 1 AND timeaccountid = 8 AND ownerid IN
(
SELECT ownerid FROM dbo.timeaccountmovement WHERE timeaccountid = 73 and ownertype = 1 AND ownerid IN --<<--
(
SELECT id FROM dbo.dutyrostershift WHERE shifttype = 1 AND employeeid = 77 AND dato BETWEEN '20230501' AND '20240331'
)
)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63502 More actions · Answer 1

I always think of EXISTS as being useful for testing if one table has ANY related records in another. (So the exact records in the related table aren't important to the question.) One example would be "find me all customers who had no purchases between two dates".

SELECT c.CustomerID
FROM Customers c
WHERE NOT EXISTS (SELECT * 
                  FROM SalesOrderHeader soh 
                  WHERE soh.CustomerID = c.CustomerID 
                  AND soh.OrderDate > @StartDate 
                  AND soh.OrderDate<@EndDate);

If you do an outer join, it reads all the related records, but EXISTS stops for each customerID when a record is found (so can eliminate the CustomerID from the outer list).

Edvard Korsbæk SSCertifiable Points: 5994 More actions · Answer 2

Dear Scott Pletcher!

Thanks

Best regards

Edvard Korsbæk