• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

[SQL] Message: Subquery returns more than 1 row

S

Shadow_

Guest
Lua:
[Error - mysql_store_result] Query: SELECT guild_wars.id, (SELECT frags FROM guild_wars WHERE status = 1) AS frags, (SELECT COUNT(1) FROM guildwar_kills WHERE guildwar_kills.warid = guild_wars.id AND guildwar_kills.killerguild = guild_wars.guild1) guild1_kills, (SELECT COUNT(1) FROM guildwar_kills WHERE guildwar_kills.warid = guild_wars.id AND guildwar_kills.killerguild = guild_wars.guild2) guild2_kills FROM guild_wars WHERE (guild1 = 5 OR guild2 = 5) AND status = 1 AND id = 9
Message: Subquery returns more than 1 row
i got this error from znote war system but editted to work with myAAC

my playerdeath.lua
Code:
local deathListEnabled = true
local maxDeathRecords = 5
local function getExperienceForLevel(lv)
    lv = lv - 1
    return ((27 * lv * lv * lv) - (100 * lv * lv) + (250 * lv)) / 6
end

local function sendWarStatus(guildId, enemyGuildId, warId, playerName, killerName)
    local guild, enemyGuild = Guild(guildId), Guild(enemyGuildId)
    if not guild or not enemyGuild then
        return
    end

    local resultId = db.storeQuery("SELECT `guild_wars`.`id`, (SELECT `frags` FROM `guild_wars` WHERE `status` = 1) AS `frags`, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild1`) guild1_kills, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild2`) guild2_kills FROM `guild_wars` WHERE (`guild1` = " .. guildId .. " OR `guild2` = " .. guildId .. ") AND `status` = 1 AND `id` = " .. warId)
    if resultId then

        local guild1_kills = result.getNumber(resultId, "guild1_kills")
        local guild2_kills = result.getNumber(resultId, "guild2_kills")
        local frags = result.getNumber(resultId, "frags")
        result.free(resultId)

        local members = guild:getMembersOnline()
        for i = 1, #members do
            members[i]:sendChannelMessage("", string.format("%s was killed by %s. The new score is %d:%d frags (frags: %d)", playerName, killerName, guild1_kills, guild2_kills, frags), TALKTYPE_CHANNEL_R1, CHANNEL_GUILD)
        end

        local enemyMembers = enemyGuild:getMembersOnline()
        for i = 1, #enemyMembers do
            enemyMembers[i]:sendChannelMessage("", string.format("%s was killed by %s. The new score is %d:%d frags (frags: %d)", playerName, killerName, guild1_kills, guild2_kills, frags), TALKTYPE_CHANNEL_R1, CHANNEL_GUILD)
        end

        if guild1_kills >= frags or guild2_kills >= frags then
            db.query("UPDATE `guild_wars` SET `status` = 4, `ended` = " .. os.time() .. " WHERE `status` = 1 AND `id` = " .. warId)
            Game.broadcastMessage(string.format("%s has just won the war against %s.", guild:getName(), enemyGuild:getName()), MESSAGE_EVENT_ADVANCE)
        end
    end
end

local function addAssistsPoints(attackerId, target)
    if not attackerId or type(attackerId) ~= 'number' then
        return
    end

    if not target or type(target) ~= 'userdata' or not target:isPlayer() then
        return
    end

    local ignoreIds = {attackerId, target:getId()}
    for id in pairs(target:getDamageMap()) do
        local tmpPlayer = Player(id)
        if tmpPlayer and not isInArray(ignoreIds, id) then
            tmpPlayer:setStorageValue(STORAGEVALUE_ASSISTS, math.max(0, tmpPlayer:getStorageValue(STORAGEVALUE_ASSISTS)) + 1)
        end
    end
end

function onDeath(player, corpse, killer, mostDamageKiller, unjustified, mostDamageUnjustified)
    local playerId = player:getId()
    if nextUseStaminaTime[playerId] ~= nil then
        nextUseStaminaTime[playerId] = nil
    end

    player:sendTextMessage(MESSAGE_EVENT_ADVANCE, "You are dead.")
    if not deathListEnabled then
        return
    end

    local byPlayer = 0
    local killerName
    if killer ~= nil then
        if killer:isPlayer() then
            byPlayer = 1
        else
            local master = killer:getMaster()
            if master and master ~= killer and master:isPlayer() then
                killer = master
                byPlayer = 1
            end
        end
        killerName = killer:getName()
    else
        killerName = "field item"
    end

    local byPlayerMostDamage = 0
    local mostDamageKillerName
    if mostDamageKiller ~= nil then
        if mostDamageKiller:isPlayer() then
            byPlayerMostDamage = 1
        else
            local master = mostDamageKiller:getMaster()
            if master and master ~= mostDamageKiller and master:isPlayer() then
                mostDamageKiller = master
                byPlayerMostDamage = 1
            end
        end
        mostDamageName = mostDamageKiller:getName()
    else
        mostDamageName = "field item"
    end

    local playerGuid = player:getGuid()
    db.query("INSERT INTO `player_deaths` (`player_id`, `time`, `level`, `killed_by`, `is_player`, `mostdamage_by`, `mostdamage_is_player`, `unjustified`, `mostdamage_unjustified`) VALUES (" .. playerGuid .. ", " .. os.time() .. ", " .. player:getLevel() .. ", " .. db.escapeString(killerName) .. ", " .. byPlayer .. ", " .. db.escapeString(mostDamageName) .. ", " .. byPlayerMostDamage .. ", " .. (unjustified and 1 or 0) .. ", " .. (mostDamageUnjustified and 1 or 0) .. ")")
    local resultId = db.storeQuery("SELECT `player_id` FROM `player_deaths` WHERE `player_id` = " .. playerGuid)

    local deathRecords = 0
    local tmpResultId = resultId
    while tmpResultId ~= false do
        tmpResultId = result.next(resultId)
        deathRecords = deathRecords + 1
    end

    if resultId ~= false then
        result.free(resultId)
    end

    local limit = deathRecords - maxDeathRecords
    if limit > 0 then
        db.asyncQuery("DELETE FROM `player_deaths` WHERE `player_id` = " .. playerGuid .. " ORDER BY `time` LIMIT " .. limit)
    end

    if byPlayer == 1 then
        local mLevel = killer:getLevel()
        local mExp = killer:getExperience()
        local mExpStage = Game.getExperienceStage(mLevel)
        local mNextExp = getExperienceForLevel(mLevel+15000)
        local mStaminaMinutes = killer:getStamina()
        local requiredExp = mNextExp - mExp
        if player:getIsVip() then
            requiredExp = requiredExp * 1.5
        elseif player:getIsExp() then
            requiredExp = requiredExp * 2
        end
        if killer:getLevel() < player:getLevel() and player:getRebirth() > killer:getRebirth() then
        killer:addItem(26438, 1)
        killer:sendTextMessage(MESSAGE_EVENT_ADVANCE, "You received 1x war token for killing ".. player:getName()..".")
        killer:addExperience(requiredExp, 1)
        end
        addAssistsPoints(killer:getId(), player)
        player:setStorageValue(STORAGEVALUE_DEATHS, math.max(0, player:getStorageValue(STORAGEVALUE_DEATHS)) + 1)
        killer:setStorageValue(STORAGEVALUE_KILLS, math.max(0, killer:getStorageValue(STORAGEVALUE_KILLS)) + 1)
        local targetGuild = player:getGuild()
        targetGuild = targetGuild and targetGuild:getId() or 0
        if targetGuild ~= 0 then
            local killerGuild = killer:getGuild()
            killerGuild = killerGuild and killerGuild:getId() or 0
            if killerGuild ~= 0 and targetGuild ~= killerGuild and isInWar(playerId, killer:getId()) then
                local warId = false
                resultId = db.storeQuery("SELECT `id` FROM `guild_wars` WHERE `status` = 1 AND ((`guild1` = " .. killerGuild .. " AND `guild2` = " .. targetGuild .. ") OR (`guild1` = " .. targetGuild .. " AND `guild2` = " .. killerGuild .. "))")
                if resultId ~= false then
                    warId = result.getDataInt(resultId, "id")
                    result.free(resultId)
                end
                if warId ~= false then
                    killer:addExperience(requiredExp, 1)
                    killer:addItem(26438, 2)
                    killer:sendTextMessage(MESSAGE_EVENT_ADVANCE, "You received 2x war token for killing guild enemy : ".. player:getName()..".")
                    addEvent(sendWarStatus, 1000, killerGuild, targetGuild, warId, player:getName(), killerName)
                    db.asyncQuery("INSERT INTO `guildwar_kills` (`killer`, `target`, `killerguild`, `targetguild`, `time`, `warid`) VALUES (" .. db.escapeString(killerName) .. ", " .. db.escapeString(player:getName()) .. ", " .. killerGuild .. ", " .. targetGuild .. ", " .. os.time() .. ", " .. warId .. ")")
                end
            end
        end
    end
end
 
@Shadow_ I believe you double checking status = 1 at line 14.
You do SELECT `frags` FROM `guild_wars` WHERE `status` = 1 and then you say AS frags, at the end of the query you have an extra AND status = 1, you might want to try to do with without the last check for status = 1 because you only need this check from the frags not for the rest of the query. As I read through your error message and your description.

Hope it works,
Ralumbi
 
Isn't the error clear enough? Put a LIMIT 1, cause sub query has to return exactly ONE result, not more or less
 
 
Solution
i couldn't add limit 1 idk how many should i add it or where exactly its a bit long
Lua:
    local resultId = db.storeQuery("SELECT `guild_wars`.`id`, (SELECT `frags` FROM `guild_wars` WHERE `status` = 1) AS `frags`, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild1`) guild1_kills, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild2`) guild2_kills FROM `guild_wars` WHERE (`guild1` = " .. guildId .. " OR `guild2` = " .. guildId .. ") AND `status` = 1 AND `id` = " .. warId)
 
i couldn't add limit 1 idk how many should i add it or where exactly its a bit long
Lua:
    local resultId = db.storeQuery("SELECT `guild_wars`.`id`, (SELECT `frags` FROM `guild_wars` WHERE `status` = 1) AS `frags`, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild1`) guild1_kills, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild2`) guild2_kills FROM `guild_wars` WHERE (`guild1` = " .. guildId .. " OR `guild2` = " .. guildId .. ") AND `status` = 1 AND `id` = " .. warId)
At the end of the query as they describe at w3schools.
 
are you sure you really need it to add limit 1 in sql query ?
lol here you go same as the line up and just add limit 1 at the end
Lua:
    local resultId = db.storeQuery("SELECT `guild_wars`.`id`, (SELECT `frags` FROM `guild_wars` WHERE `status` = 1) AS `frags`, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild1`) guild1_kills, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild2`) guild2_kills FROM `guild_wars` WHERE (`guild1` = " .. guildId .. " OR `guild2` = " .. guildId .. ") AND `status` = 1 AND `id` = " .. warId LIMIT 1)
 
are you sure you really need it to add limit 1 in sql query ?
lol here you go same as the line up and just add limit 1 at the end
Lua:
    local resultId = db.storeQuery("SELECT `guild_wars`.`id`, (SELECT `frags` FROM `guild_wars` WHERE `status` = 1) AS `frags`, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild1`) guild1_kills, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild2`) guild2_kills FROM `guild_wars` WHERE (`guild1` = " .. guildId .. " OR `guild2` = " .. guildId .. ") AND `status` = 1 AND `id` = " .. warId LIMIT 1)

Lua:
local resultId = db.storeQuery("SELECT `guild_wars`.`id`, (SELECT `frags` FROM `guild_wars` WHERE `status` = 1) AS `frags`, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild1`) guild1_kills, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild2`) guild2_kills FROM `guild_wars` WHERE (`guild1` = " .. guildId .. " OR `guild2` = " .. guildId .. ") AND `status` = 1 AND `id` = " .. warId .. " LIMIT 1 "
 
Do you know where is subquery in a query?
aff.. SELECT `frags` FROM `guild_wars` WHERE `status` = 1 it needs to be added here
Post automatically merged:

@Shadow_ this is your solution:
Lua:
local resultId = db.storeQuery("SELECT `guild_wars`.`id`, (SELECT `frags` FROM `guild_wars` WHERE `status` = 1 LIMIT 1) AS `frags`, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild1`) guild1_kills, (SELECT COUNT(1) FROM `guildwar_kills` WHERE `guildwar_kills`.`warid` = `guild_wars`.`id` AND `guildwar_kills`.`killerguild` = `guild_wars`.`guild2`) guild2_kills FROM `guild_wars` WHERE (`guild1` = " .. guildId .. " OR `guild2` = " .. guildId .. ") AND `status` = 1 AND `id` = " .. warId)
 
Last edited:
Back
Top