Any Edit for This query

09/25/2014 12:21 where_love2003#1
Morning all Brother
can any one help me for edit query to work to search buy itemcode and and add maxcount items
Code:
USE SRO_VT_ACCOUNT

         DECLARE @Serial bigint
             declare @CID int
             declare @AID int
             declare @ItemCode varchar(50)
             declare @loc varchar(50)
             set @loc = 'inventory'
         ---------------------------------------------------
             SET @Serial = YOUR_SERIAL_HERE
         ---------------------------------------------------
             SET @CID = (select CharID from SRO_VT_SHARD.dbo._Inventory where ItemID = (select ID64 from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))
             if @CID is null
             begin
             SET @AID = (select UserJID from SRO_VT_SHARD.dbo._Chest where ItemID = (select ID64 from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))
             SET @loc = 'chest'
             end
             else if @AID is null
             begin
             SET @AID = (select UserJID from SRO_VT_SHARD.dbo._User where CharID = (select OwnerCharID from SRO_VT_SHARD.dbo._CharCOS where ID = (select COSID from SRO_VT_SHARD.dbo._InvCOS where ItemID = (select ID64 from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))))
             SET @loc = 'pet'
             end
             else
             begin
             SET @AID = (select UserJID from SRO_VT_SHARD.dbo._User where CharID = @CID)
             end
             SET @ItemCode = (select CodeName128 from SRO_VT_SHARD.dbo._RefObjCommon where ID = (select RefItemID from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))
             SELECT top 1 tbuser.StrUserID as AccID, chart.CharName16 as CharName, @Serial as Serial, @ItemCode as ItemCode, @loc as Location
             FROM TB_User as tbuser
             JOIN SRO_VT_SHARD.dbo._AccountJID as accjid on tbuser.JID = accjid.JID
             JOIN SRO_VT_SHARD.dbo._User as usert on accjid.JID = usert.UserJID
             JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
             WHERE usert.UserJID = @AID
09/25/2014 16:01 Muhab*#2
please explain more....
09/25/2014 21:23 Trinity^#3
What exactly are you trying to achieve with this query? because its a fucking disaster...

Are you just trying to find the user that has the item with the set serial.
09/26/2014 16:35 TrypticoN#4
Yea, need more information about what you really want to achieve with this query, cause well its a total disaster lol.
09/26/2014 17:16 where_love2003#5
we need to search items from invcos , inventory , Storage By char
and we need how much have char items like Gold,Silver coin
sorry my bad language English

and thanks every one try help me
09/26/2014 19:52 Syloxx#6
[Only registered and activated users can see links. Click Here To Register...]
09/26/2014 20:29 where_love2003#7
i`know it but i want edit this query to search by itemsCode or Items ID
09/26/2014 21:39 ​Exo#8
Quote:
Originally Posted by where_love2003 View Post
i`know it but i want edit this query to search by itemsCode or Items ID
PHP Code:
USE SRO_VT_SHARD
GO

--//////////FindItemBySerial V1.3 By Syloxx\\\\\\\\\
DECLARE @RefID BIGINT = (SELECT ID FROM _RefObjCommon WHERE CodeName128='YourCodeHere'),
        @ItemID INT,
        @ItemCodeName VARCHAR(128)

        SELECT    @ItemCodeName = C.CodeName128 , 
                @ItemID = I.ID64
        FROM _RefObjCommon C
        INNER JOIN _Items I
        ON C.ID = I.RefItemID
        WHERE @RefID = I.RefItemID


        --###############################################
        --#####       CHECK IF SERIAL IS VALID      #####
        --###############################################
        IF @ItemID IS NULL OR @ItemID = 0
        BEGIN
            PRINT 'No item with the specified Serial found!'
        END


        --###############################################
        --#####    CHECK IF ITEM IS IN INVENTORY    #####
        --###############################################
        ELSE IF (@ItemID IN (SELECT ItemID from _Inventory))
        BEGIN
            SELECT    TBU.JID AS 'UserJID',
                    TBU.StrUserID AS 'UserName',
                    C.CharID,
                    C.CharName16 AS 'CharName',
                    @ItemCodeName AS 'Item CodeName',
                    IT.OptLevel,
                    'Inventory' AS 'Storage',
                    INV.Slot
            FROM _Items IT
            INNER JOIN _Inventory INV
            ON INV.ItemID = IT.ID64
            INNER JOIN _Char C
            ON C.CharID = INV.CharID
            INNER JOIN _User U
            ON U.CharID = C.CharID
            INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
            ON TBU.JID = U.UserJID
            WHERE IT.RefItemID = @RefID
        END


        --###############################################
        --#####     CHECK IF ITEM IS IN STORAGE     #####
        --###############################################
        ELSE IF (@ItemID IN (SELECT ItemID FROM _Chest))
        BEGIN
            SELECT    TBU.JID AS 'UserJID',
                    TBU.StrUserID AS 'UserName',
                    C.CharID,
                    C.CharName16 AS 'CharName',
                    @ItemCodeName AS 'Item CodeName',
                    IT.OptLevel,
                    'Storage' AS 'Storage',
                    SINV.Slot
            FROM _Items IT
            INNER JOIN _Chest SINV
            ON SINV.ItemID = IT.ID64
            INNER JOIN _User U
            ON U.UserJID = SINV.UserJID
            INNER JOIN _Char C
            ON C.CharID = U.CharID
            INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
            ON TBU.JID = U.UserJID
            WHERE IT.RefItemID = @RefID
        END


        --###############################################
        --#####       CHECK IF ITEM IS IN PET       #####
        --###############################################
        ELSE IF (@ItemID IN (SELECT ItemID FROM _InvCOS))
        BEGIN
            DECLARE @COSSerial BIGINT,
                    @COSItemID INT,
                    @COSCodeName VARCHAR(128)

            SELECT    CCOS.ID AS 'COSID',
                    ROC.CodeName128 AS 'COS CodeName',
                    CCOS.CharName AS 'COS Name',
                    @ItemCodeName AS 'Item CodeName',
                    IT.OptLevel,
                    'COS Inventory' AS 'Storage',
                    CINV.Slot
            FROM _Items IT
            INNER JOIN _InvCOS CINV
            ON CINV.ItemID = IT.ID64
            INNER JOIN _CharCOS CCOS
            ON CCOS.ID = CINV.COSID
            INNER JOIN _Items COSIT
            ON COSIT.Data = CCOS.ID
            INNER JOIN _RefObjCommon ROC
            ON ROC.ID = COSIT.RefItemID
            WHERE IT.RefItemID = @RefID
            AND ROC.TypeID2=2
            AND ROC.TypeID3=1
            AND ROC.TypeID4=2


            SELECT    @COSSerial = COSIT.Serial64,
                    @COSItemID = COSIT.ID64,
                    @COSCodeName = ROC.CodeName128
            FROM _Items IT
            INNER JOIN _InvCOS CINV
            ON CINV.ItemID = IT.ID64
            INNER JOIN _CharCOS CCOS
            ON CCOS.ID = CINV.COSID
            INNER JOIN _Items COSIT
            ON COSIT.Data = CCOS.ID
            INNER JOIN _RefObjCommon ROC
            ON ROC.ID = COSIT.RefItemID
            WHERE IT.RefItemID = @RefID
            AND ROC.TypeID2=2
            AND ROC.TypeID3=1
            AND ROC.TypeID4=2


            --##############################################
            --#####    CHECK IF PET IS IN INVENTORY    #####
            --##############################################
            IF (@COSItemID IN (SELECT ItemID from _Inventory))
            BEGIN
                SELECT    TBU.JID AS 'UserJID',
                        TBU.StrUserID AS 'UserName',
                        C.CharID,
                        C.CharName16 AS 'CharName',
                        @COSCodeName AS 'COS CodeName',
                        IT.OptLevel,
                        'Inventory' AS 'Storage',
                        INV.Slot
                FROM _Items IT
                INNER JOIN _Inventory INV
                ON INV.ItemID = IT.ID64
                INNER JOIN _Char C
                ON C.CharID = INV.CharID
                INNER JOIN _User U
                ON U.CharID = C.CharID
                INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
                ON TBU.JID = U.UserJID
                WHERE IT.Serial64 = @COSSerial
            END


            --###############################################
            --#####     CHECK IF ITEM IS IN STORAGE     #####
            --###############################################
            ELSE IF (@COSItemID IN (SELECT ItemID FROM _Chest))
            BEGIN
                SELECT    TBU.JID AS 'UserJID',
                        TBU.StrUserID AS 'UserName',
                        C.CharID,
                        C.CharName16 AS 'CharName',
                        @COSCodeName AS 'COS CodeName',
                        IT.OptLevel,
                        'Storage' AS 'Storage',
                        SINV.Slot
                FROM _Items IT
                INNER JOIN _Chest SINV
                ON SINV.ItemID = IT.ID64
                INNER JOIN _User U
                ON U.UserJID = SINV.UserJID
                INNER JOIN _Char C
                ON C.CharID = U.CharID
                INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
                ON TBU.JID = U.UserJID
                WHERE IT.Serial64 = @COSSerial
            END


            --##############################################
            --#####  CHECK IF PET IS IN GUILD STORAGE  #####
            --##############################################
            ELSE IF (@COSItemID IN (SELECT ItemID FROM _GuildChest))
            BEGIN
                SELECT    TBU.JID AS 'UserJID',
                        TBU.StrUserID AS 'UserName',
                        GM.CharID,
                        GM.CharName AS 'GuildMaster',
                        G.ID AS 'GuildID',
                        G.Name AS 'GuildName',
                        @COSCodeName AS 'COS CodeName',
                        IT.OptLevel,
                        'Guild Storage' AS 'Storage',
                        GINV.Slot
                FROM _Items IT
                INNER JOIN _GuildChest GINV
                ON GINV.ItemID = IT.ID64
                INNER JOIN _Guild G
                ON G.ID = GINV.GuildID
                INNER JOIN _GuildMember GM
                ON GM.GuildID = G.ID
                INNER JOIN _User U
                ON U.CharID = GM.CharID
                INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
                ON TBU.JID = U.UserJID
                WHERE IT.Serial64 = @COSSerial
                AND GM.MemberClass = 0
            END
        END


        --###############################################
        --#####  CHECK IF ITEM IS IN GUILD STORAGE  #####
        --###############################################
        ELSE IF (@ItemID IN (SELECT ItemID FROM _GuildChest))
        BEGIN
            SELECT    TBU.JID AS 'UserJID',
                    TBU.StrUserID AS 'UserName',
                    GM.CharID,
                    GM.CharName AS 'GuildMaster',
                    G.ID AS 'GuildID',
                    G.Name AS 'GuildName',
                    @ItemCodeName AS 'Item CodeName',
                    IT.OptLevel,
                    'Guild Storage' AS 'Storage',
                    GINV.Slot
            FROM _Items IT
            INNER JOIN _GuildChest GINV
            ON GINV.ItemID = IT.ID64
            INNER JOIN _Guild G
            ON G.ID = GINV.GuildID
            INNER JOIN _GuildMember GM
            ON GM.GuildID = G.ID
            INNER JOIN _User U
            ON U.CharID = GM.CharID
            INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
            ON TBU.JID = U.UserJID
            WHERE IT.RefItemID = @RefID
            AND GM.MemberClass = 0
        END