how to count rows and still display all rows? mysql -

hello im trying count number of rows each employee salary.

i have 2 tables ex.

table employee

id_____________name  1..............sam  2..............greg  3..............vic  4..............steve 

table salary

id____________salary  1.............10000  1.............15000  2.............30000  3.............13000  4.............90000  4.............20000 

the result need is

id| name | salary| count  1 | sam  | 10000 | 2  1 | sam  | 15000 | 2  2 | greg | 30000 | 1  3 | vic  | 13000 | 1  4 |steve | 90000 | 2  4 |steve | 90000 | 2 

to summarize: have 4 employees past salaries. i'm trying create query shows list of salaries , how many salaries employee has.

here have tried 6 count...

create temporary table rates select e.id, e.name, s.salary, count(*) count employee e inner join salary s on e.id = s.id group name, s.salary order name;  create temporary table sum select r.id, sum(count) sum rates r;  select * rates left outer join sum s on r.id = s.id; 

i've tried self join didn't work.

thanks in advance!

you want perform inner join between salary , employees tables

you want include nested select statement.

select e.id, e.name, s.salary,  (select count(*) salary s        employee_id = id) count employees e inner join salary s on employees.id = salary.id 

here fiddle

http://sqlfiddle.com/#!9/b94ec/9/0