t, MySQL - rextester

Language: Layout:

DROP TABLE IF EXISTS Quests;
DROP TABLE IF EXISTS NPCs;
DROP TABLE IF EXISTS QuestStages;
DROP TABLE IF EXISTS LinkedQuests;

CREATE TABLE Quests 
    (`ID` int, `QuestNumber` int, `Name` varchar(8), `NPCID` int, `RewardXP` int, `RewardMoney` int, `LevelID` int, `Text` varchar(9), `CompletedText` varchar(11))
;
    
INSERT INTO Quests 
    (`ID`, `QuestNumber`, `Name`, `NPCID`, `RewardXP`, `RewardMoney`, `LevelID`, `Text`, `CompletedText`)
VALUES
    (1, 1, '"Quest1"', 1, 10, 10, 1, '"do this"', '"Well Done"'),
    (2, 2, '"Quest2"', 1, 10, 10, 1, '"do this"', '"Well Done"'),
    (3, 3, '"Quest3"', 2, 10, 10, 1, '"do this"', '"Well Done"'),
    (4, 4, '"Quest4"', 2, 10, 10, 1, '"do this"', '"Well Done"'),
    (5, 5, '"Quest5"', 2, 10, 10, 1, '"do this"', '"Well Done"'),
    (6, 6, '"Quest6"', 3, 10, 10, 1, '"do this"', '"Well Done"')
;

CREATE TABLE NPCs
    (`ID` int, `RoomID` int)
;
    
INSERT INTO NPCs
    (`ID`, `RoomID`)
VALUES
    (1, 1),
    (2, 1),
    (3, 2)
;

CREATE TABLE QuestStages 
    (`ID` int, `UserID` int, `QuestID` int, `Accepted` varchar(5), `Complete` varchar(5))
;
    
INSERT INTO QuestStages 
    (`ID`, `UserID`, `QuestID`, `Accepted`, `Complete`)
VALUES
    (1, 1, 2, 'TRUE', 'FALSE'),
    (2, 1, 3, 'TRUE', 'TRUE'),
    (3, 1, 4, 'FALSE', 'FALSE'),
    (4, 1, 5, 'TRUE', 'TRUE'),
    (5, 1, 6, 'TRUE', 'FALSE')
;

CREATE TABLE LinkedQuests
    (`QuestID` int, `PreQuestID` int)
;
    
INSERT INTO LinkedQuests
    (`QuestID`, `PreQuestID`)
VALUES
    (2, 1),
    (5, 4),
    (4, 3)
;

-- First filter the quest you havent complete yet
SELECT *
FROM QuestStages
WHERE `UserID` = 1
  AND `Complete` = 'FALSE';
  
-- Then Join to make sure you have the requirment
SELECT *
FROM QuestStages Q
LEFT JOIN LinkedQuests LQ
  ON Q.`QuestID` = LQ.`QuestID`
LEFT JOIN QuestStages Pre
  ON Q.`UserID` = Pre.`UserID`
 AND LQ.`PreQuestID` = Pre.`QuestID`
WHERE Q.`UserID` = 1
  AND Q.`Complete` = 'FALSE'
  AND (Pre.`Complete` = 'TRUE' OR Pre.`Complete` IS NULL)  
  -- Prerequirement is complete or doesnt have Prerequirement
ORDER BY Q.`QuestID`
  ;

-- Final Output, Join to get quest details
SELECT QS.`QuestID`, QS.`Accepted`, Q.`Name`, Q.`Text`, Q.`RewardMoney`
FROM QuestStages QS
LEFT JOIN LinkedQuests LQ
  ON QS.`QuestID` = LQ.`QuestID`
LEFT JOIN QuestStages Pre
  ON QS.`UserID` = Pre.`UserID`
 AND LQ.`PreQuestID` = Pre.`QuestID`
JOIN Quests Q
  ON QS.`QuestID` = Q.`QuestNumber`
WHERE QS.`UserID` = 1
  AND QS.`Complete` = 'FALSE'
  AND (Pre.`Complete` = 'TRUE' OR Pre.`Complete` IS NULL)  
  -- Prerequirement is complete or doesnt have Prerequirement
ORDER BY QS.`QuestID`
  ;


absolute service time: 0,41 sec