I need a replacement NOT IN with join . How do I do this because the results are different with this query.
SELECT COUNT(*) as total FROM requests
WHERE
isApproved='1' AND
softDelete='0' AND
requestID NOT IN (
SELECT
DISTINCT requestID
FROM consulting
WHERE
softDelete='0' AND
isApproved='1' AND
)
CodePudding user response:
SELECT COUNT(*) as total FROM requests AS R
LEFT JOIN
(
SELECT
DISTINCT requestID
FROM consulting
WHERE
softDelete='0' AND
isApproved='1' AND
) X ON R.requestID =X.requestID
WHERE R.isApproved='1' AND R.softDelete='0'
AND X.requestID IS NULL
Could you please try something like this