CODEWITHSUNDEEP
mysqlsql
Dean
Dean

Reputation: 517

mysql joning results with multiple results

lets say i have a table called player

profileid, player

and a table called bank

id, profileid, player, item

lets say i have the follow entry for player

table player: [1, verro]

lets say i have 5 entires into bank like this:

table bank: [1, 1, verro, "gold bar"]
table bank: [2, 1, verro, "gold bar"]
table bank: [4, 1, verro, "gold bar"]
table bank: [5, 1, verro, "snake"]

how can i build a query with the following conditions?:

select player with the name verro then sum up how many gold bars he has?

the result should be: ["verro", 3]

i was attempting to do this:

select *, count(item) from player_deposit bank
join player_attributes pa on bank.player = pa.player
where LOWER(bank.player) = "verro" && bank.item = "Gold Bar"

but did not work for me, and was slow.

Upvotes: 0

Views: 27

Answers (1)

Stidgeon
Stidgeon

Reputation: 2723

Because all the info you need is in the bank table, you don't need your join. Just run a query like this:

SELECT player, COUNT(id) 
FROM bank
WHERE player = 'verro' AND
item = 'gold bar'

And you should get what you need. In fact, the way your tables are set up, the player table is redundant with the info in bank table.

Upvotes: 1

Related Questions