Reputation: 13
i have a problem & I'm new in this.
i have a table called "items" and there is IDs column and count column also i got a characters table which are the owners of the items ids
i have here a code that working..and shows the top character with the item and its count.. to explain more..
"SELECT SUM(it.count) FROM characters ch
LEFT OUTER JOIN items it ON ch.charId=it.owner_id
WHERE item_id=40001 ORDER BY SUM(it.count) DESC LIMIT 20");)
So what i want exactly is to make this takes a list of a second item id from same items table and same column count ...
anyone can help me ?
Upvotes: 0
Views: 1697
Reputation: 6882
For a start, let me make your query a bit more readable:
SELECT SUM(it.count)
FROM characters ch
LEFT OUTER JOIN items it ON ch.charId=it.owner_id
WHERE item_id=40001
ORDER BY SUM(it.count) DESC
LIMIT 20
This query is a bit weird. First you are selecting one sum (you are not grouping by anything) then you are orderbing by that sum, and limiting the number of returned rows to 20?
Also, you're doing a LEFT OUTER JOIN
but then using the outer table in your WHERE
table, which results in all rows being removed that only have a character but no item.
The following query would do the exact same thing.
SELECT SUM(it.count)
FROM characters ch
JOIN items it ON ch.charId=it.owner_id
WHERE item_id=40001
Now if you want more than one item_id, you either want the total count of both:
SELECT SUM(it.count)
FROM characters ch
JOIN items it ON ch.charId=it.owner_id
WHERE item_id IN (40001,40002)
Or you want sums for each item
SELECT SUM(it.count)
FROM characters ch
JOIN items it ON ch.charId=it.owner_id
GROUP BY item_id
ORDER BY SUM(it.count) DESC
LIMIT 20
You could change the JOIN to do just a few item types:
SELECT SUM(it.count)
FROM characters ch
JOIN items it ON ch.charId=it.owner_id AND it.item_id IN (40001,40002)
GROUP BY item_id
ORDER BY SUM(it.count) DESC
LIMIT 20
Upvotes: 2