Check Items !

marawan.mourad · 10/05/2015, 02:13

Hello , Dears
iam have small private silkroad
and i want know player who owns more Arena Coins in my game ?
how i can know , which player have more than 10.000 Arena Coin in game

thanks

Exo · 10/05/2015, 05:01

man neqst time u ask at correctlar sektion okk? cuz this wrong one and u need ask there.

oqe man? this man works full wokrs man

PHP Code:


			
SELECT    CharName16
FROM    _Char 
WHERE    CharID    IN
                    (
                        SELECT I.CharID
                        FROM    _Inventory I
                        JOIN    _Items    T
                        ON    I.ItemID = T.ID64
                        WHERE T.RefItemID = 25834 AND T.Data = 10000
                    )
--Men wrks only if in inventory cuz to lazy to srch other things man. Pet/Chest/etc man. I will write query menz when back oqe?

PortalDark · 10/05/2015, 05:34

Quote:

Originally Posted by Exo

man neqst time u ask at correctlar sektion okk? cuz this wrong one and u need ask there.

oqe man? this man works full wokrs man

PHP Code:

SELECT CharName16 FROM _Char WHERE CharID IN ( SELECT I.CharID FROM _Inventory I JOIN _Items T ON I.ItemID = T.ID64 WHERE T.RefItemID = 25834 AND T.Data = 10000 ) --Men wrks only if in inventory cuz to lazy to srch other things man. Pet/Chest/etc man. I will write query menz when back oqe?

no need to talk like an asshat

AceSpace · 10/05/2015, 10:45

Here, it'll check the pet inventory and storage and inventory, it might not work well.. though i did my best.

Code:

USE SRO_VT_SHARD

DECLARE @ItemCodeNameToCheck varchar(max) = 'ITEM_ETC_ARENA_COIN';

CREATE TABLE #_mGotArena (
CharName16 VARCHAR(32) NOT NULL,
CodeName128 VARCHAR(MAX) NOT NULL,
Count INT NOT NULL)


INSERT INTO #_mGotArena
SELECT C.CharName16, R.CodeName128, IT.Data
FROM _Char C
JOIN _Inventory I ON C.CharID = I.CharID
JOIN _Items IT ON I.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
--JOIN _Chest CT ON IT.ID64 = CT.ItemID
WHERE R.CodeName128 = @ItemCodeNameToCheck

DECLARE @CharName varchar(32)
SELECT @CharName = C.CharName16
FROM _Char C
JOIN _User U ON C.CharID = U.CharID
JOIN _Chest CT ON U.UserJID = CT.UserJID
JOIN _Items IT ON CT.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
WHERE R.CodeName128 = @ItemCodeNameToCheck

IF NOT EXISTS (SELECT CharName16 FROM #_mGotArena WHERE CharName16 = @CharName)
INSERT INTO #_mGotArena
SELECT C.CharName16, R.CodeName128, IT.Data
FROM _Char C
JOIN _User U ON C.CharID = U.CharID
JOIN _Chest CT ON U.UserJID = CT.UserJID
JOIN _Items IT ON CT.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
WHERE R.CodeName128 = @ItemCodeNameToCheck
ELSE
UPDATE U
SET Count += IT.Data
FROM #_mGotArena U
JOIN _Char C ON U.CharName16 = C.CharName16
JOIN _User UX ON C.CharID = UX.CharID
JOIN _Chest CT ON UX.UserJID = CT.UserJID
JOIN _Items IT ON CT.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
WHERE R.CodeName128 = @ItemCodeNameToCheck

DECLARE @CharnameX varchar(32)
SELECT @CharnameX = C.CharName16 FROM _Char C
JOIN _CharCOS CC ON C.CharID = CC.RefCharID

IF NOT EXISTS (SELECT CharName16 FROM #_mGotArena WHERE CharName16 = @CharnameX)
INSERT INTO #_mGotArena
SELECT C.CharName16, R.CodeName128, IT.Data
FROM _Char C
JOIN _CharCOS CC ON C.CharID = CC.RefCharID
JOIN _InvCOS IC ON CC.ID = IC.COSID
JOIN _Items IT ON IC.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
WHERE R.CodeName128 = @ItemCodeNameToCheck
ELSE
UPDATE U
SET Count += IT.Data
FROM #_mGotArena U
JOIN _Char C ON U.CharName16 = C.CharName16
JOIN _CharCOS CC ON C.CharID = CC.RefCharID
JOIN _InvCOS IC ON CC.ID = IC.COSID
JOIN _Items IT ON IC.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
WHERE R.CodeName128 = @ItemCodeNameToCheck


SELECT CharName16, CodeName128, Count
FROM #_mGotArena
WHERE Count > 9999

Syloxx · 10/05/2015, 11:23

Quote:

Originally Posted by Skipper*

Here, it'll check the pet inventory and storage and inventory, it might not work well.. though i did my best.

Code:

USE SRO_VT_SHARD

DECLARE @ItemCodeNameToCheck varchar(max) = 'ITEM_ETC_ARENA_COIN';

CREATE TABLE _UhmUhm (
CharName16 VARCHAR(32) NOT NULL,
CodeName128 VARCHAR(MAX) NOT NULL,
Count INT NOT NULL)


INSERT INTO _UhmUhm
SELECT C.CharName16, R.CodeName128, IT.Data
FROM _Char C
JOIN _Inventory I ON C.CharID = I.CharID
JOIN _Items IT ON I.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
--JOIN _Chest CT ON IT.ID64 = CT.ItemID
WHERE R.CodeName128 = @ItemCodeNameToCheck

DECLARE @CharName varchar(32)
SELECT @CharName = C.CharName16
FROM _Char C
JOIN _User U ON C.CharID = U.CharID
JOIN _Chest CT ON U.UserJID = CT.UserJID
JOIN _Items IT ON CT.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
WHERE R.CodeName128 = @ItemCodeNameToCheck

IF NOT EXISTS (SELECT CharName16 FROM _UhmUhm WHERE CharName16 = @CharName)
INSERT INTO _UhmUhm
SELECT C.CharName16, R.CodeName128, IT.Data
FROM _Char C
JOIN _User U ON C.CharID = U.CharID
JOIN _Chest CT ON U.UserJID = CT.UserJID
JOIN _Items IT ON CT.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
WHERE R.CodeName128 = @ItemCodeNameToCheck
ELSE
UPDATE U
SET Count += IT.Data
FROM _UhmUhm U
JOIN _Char C ON U.CharName16 = C.CharName16
JOIN _User UX ON C.CharID = UX.CharID
JOIN _Chest CT ON UX.UserJID = CT.UserJID
JOIN _Items IT ON CT.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
WHERE R.CodeName128 = @ItemCodeNameToCheck

DECLARE @CharnameX varchar(32)
SELECT @CharnameX = C.CharName16 FROM _Char C
JOIN _CharCOS CC ON C.CharID = CC.RefCharID

IF NOT EXISTS (SELECT CharName16 FROM _UhmUhm WHERE CharName16 = @CharnameX)
INSERT INTO _UhmUhm
SELECT C.CharName16, R.CodeName128, IT.Data
FROM _Char C
JOIN _CharCOS CC ON C.CharID = CC.RefCharID
JOIN _InvCOS IC ON CC.ID = IC.COSID
JOIN _Items IT ON IC.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
WHERE R.CodeName128 = @ItemCodeNameToCheck
ELSE
UPDATE U
SET Count += IT.Data
FROM _UhmUhm U
JOIN _Char C ON U.CharName16 = C.CharName16
JOIN _CharCOS CC ON C.CharID = CC.RefCharID
JOIN _InvCOS IC ON CC.ID = IC.COSID
JOIN _Items IT ON IC.ItemID = IT.ID64
JOIN _RefObjCommon R ON IT.RefItemID = R.ID
WHERE R.CodeName128 = @ItemCodeNameToCheck


SELECT CharName16, CodeName128, Count
FROM _UhmUhm
WHERE Count > 9999

next time, if you wanna use a table for such stuff use a variable table or atlease a temp table but dont fill people databases with tables they habe such a retarted name.

AceSpace · 10/05/2015, 12:00

Quote:

Originally Posted by Syloxx

next time, if you wanna use a table for such stuff use a variable table or atlease a temp table but dont fill people databases with tables they habe such a retarted name.

Oh oh you're so fantastic, why didn't you help instead? I didn't care about the names because he'll just use it once or twice not all the time.

However, I updated the query and changed the table to a temp one.

Syloxx · 10/05/2015, 12:03

Quote:

Originally Posted by Skipper*

Oh oh you're so fantastic, why didn't you help instead? I didn't care about the names because he'll just use it once or two not all the time.

However, I updated the query and changed the table to a temp one.

well, did you saw me active in elitepvpers the last days (or even weeks)? so how should i help people if i wasnt active?

and yes, he use it only once or maybe twice (or shall i say two) so why using a permanent table?

Exo · 10/05/2015, 12:17

Quote:

Originally Posted by PortalDark

no need to talk like an asshat

Why u no move this man?!

PortalDark · 10/05/2015, 12:46

#moved to correct section

~~Anonymous-6723~~ · 10/10/2015, 18:34

Dat should be possible in much less code..

Exo · 10/10/2015, 19:32

Quote:

Originally Posted by Zed*

Dat should be possible in much less code..

If it's just a one-time use then it wouldn't really matter tho.