Help with query ...

[noob2017]

Hi ..

I have a query to select Item points from char:

select SUM(_Items.OptLevel) as ItemPoints
from _Char
right join _Inventory ON _Inventory.CharID =_Char.CharID
right join _Items ON _Items.ID64 in (_Inventory.ItemID)
where _Inventory.Slot between 0 and 12 and _Char.CharID = @CharID

Its work well... But i want add also info from: _InventoryForAvatar where slot = 4 (devil spirit +)

Who can help me сombine a query?

[noob2017]

bump -.-

[Muhab*]

This should work.

SELECT SUM(OptLevel) ItemPoints
FROM
(
SELECT ItemID FROM _Inventory WHERE Slot BETWEEN 0 AND 12 AND ItemID != 0 AND CharID = @CharID
UNION ALL
SELECT ItemID FROM _InventoryForAvatar WHERE Slot = 4 AND ItemID != 0 AND CharID = @CharID
)
a JOIN _Items b ON a.ItemID = b.ID64

[noob2017]

Originally Posted by Muhab Ashraf

This should work.

Ye this work well, thanks.

But could you please edit this query also .. (for web)

select TOP 30 ROW_NUMBER() OVER(order by Level desc, Exp desc, SUM (_items.Optlevel) desc) AS Rank, _Char.CharName16,_Char.NickName16,SUM(_Items.OptLe vel) as ItemPoints,Level, Exp
from _Char
INNER JOIN _CharTrijob ON _CharTrijob.CharID=_Char.CharID
INNER JOIN _Inventory ON _Inventory.CharID =_Char.CharID
INNER join _Items ON _Items.ID64 = _Inventory.ItemID
where _Inventory.Slot between 0 and 12 and JobType = 1 and ItemID != 0
group by _Char.CharName16,_Char.NickName16,Level, Exp
order by Level desc, Exp desc, SUM (_items.Optlevel) desc

[Muhab*]

Originally Posted by noob2017

Ye this work well, thanks.

But could you please edit this query also .. (for web)

SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY SUM(OptLevel) DESC) Rank, c.CharName16, SUM(OptLevel) ItemPoints, c.CurLevel
FROM
(
SELECT CharID, ItemID FROM _Inventory WHERE Slot BETWEEN 0 AND 12 AND ItemID != 0-- AND CharID = @CharID
UNION ALL
SELECT CharID, ItemID FROM _InventoryForAvatar WHERE Slot = 4 AND ItemID != 0-- AND CharID = @CharID
)
a JOIN _Items b ON a.ItemID = b.ID64
JOIN _Char c ON c.CharID = a.CharID
GROUP BY c.CharName16, c.CurLevel

Here is the base working query, You can select more columns and join more tables.