Why do these 2 queries return different results?
I have 2 queries that appear to me to be logically the same.
They are returning different counts.
I need the count of all surveys where
the SolutionID = 3, the division is 2 and the fuel type is 1.
However, I also need access to tblRec1 and tblRec2.
Why are they returning different counts?
How can I get an accurate count AND have access to tblRec1 and tblRec2?
SELECT COUNT(DISTINCT surveys.RecordID)
FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID
WHERE surveys.SolutionID = 3 AND surveys.RecordID IN
(SELECT SurveyID FROM answers WHERE AttributionID IN
(SELECT AttributionID FROM attrib_organization3_rl WHERE FieldID
IN (SELECT TABLE1ID FROM organization3_division3 WHERE TABLE2ID = 2)))
AND surveys.RecordID IN
(SELECT SurveyID FROM answers WHERE AttributionID IN
(SELECT AttributionID FROM attrib_forklift3_rl WHERE FieldID
IN (SELECT RecordID FROM forklift3 WHERE lnk_fuel_type = 1)))
SELECT COUNT(DISTINCT surveys.RecordID) AS Cnt
FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID
INNER JOIN attrib_organization3_rl AS tblAtr
INNER JOIN organization3_division3 AS tblMlt
INNER JOIN division3 AS tblRec1
ON tblMlt.Table2ID = tblRec1.RecordID
ON tblAtr.FieldID = tblMlt.Table1ID
ON answers.AttributionID = tblAtr.AttributionID
WHERE surveys.SolutionID = 3 AND tblRec1.RecordID = 2 AND surveys.RecordID IN (
SELECT DISTINCT SurveyID FROM answers
INNER JOIN attrib_forklift3_rl AS tblAtr
INNER JOIN forklift3 AS tblLnkd
INNER JOIN fuel_type3 AS tblRec2
ON tblLnkd.lnk_fuel_type = tblRec2.RecordID
ON tblAtr.FieldID = tblRec2.RecordID
ON answers.AttributionID = tblAtr.AttributionID
WHERE tblRec2.RecordID = 1)
Subject
Written By
Posted
Why do these 2 queries return different results?
June 18, 2025 02:25PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.