sql - trying to count and join on two mysql tables -

i've got mysql db 2 tables:

db.names

name | level ------------ bob    4 john   3 andy   2 dave   1 

db.data

entry | user ------------ dfds    bob hdes    bob sers    john iuid    dave yyuy    john 

i'm trying count count how many entrys each user has made , show level. lit looks this:

count(*) | user | level ----------------------- 2         bob     4 2         john    3 1         dave    1 

i've tried use left join , distinct join, can seem grips logic. greatlly appreciated

you need joins tables using inner join since want show record both exist on 2 tables. , count number of instances, need use aggregate function count() , group by clause.

select  count(*) totalcount,         a.name,         a.level    names         inner join data b             on a.name = b.user group   a.name, a.level 

• sqlfiddle demo

to further gain more knowledge joins, kindly visit link below:

• visual representation of sql joins

output

╔════════════╦══════╦═══════╗ ║ totalcount ║ name ║ level ║ ╠════════════╬══════╬═══════╣ ║          2 ║ bob  ║     4 ║ ║          2 ║ john ║     3 ║ ║          1 ║ dave ║     1 ║ ╚════════════╩══════╩═══════╝