sql - How to join 2 tables with select and count in single query -

i need join 2 tables (person , personline). result should contain id , name column person table , count of personlineid column personline table each id. sql query returns count of personlineid. can form sql.

person:

id          name            age 100         john            25 101         james           30 102         janet           35 

personline:

id          name            personlineid 100         john            1 100         john            2 100         john            3 101         james           1 101         james           2 102         janet           1 

sql:

select p.id, cnt.count_personline, p.name      person p      left join personline pl      on p.id = pl.id,     (select count(pl.personlineid) cnt person p left join personline pl on p.id = pl.id      p.id = pl.id) cnt 

join table (expected):

id              count_personline            name 100             3                           john 101             2                           james 102             1                           janet 

join table (actual):

id              count_personline            name 100             6                           john 101             6                           james 102             6                           janet 

with sample data, don't need person table -- because seem have redundant table in 2 tables. should fix this, but:

select pl.id, pl.name, count(*) personline pl group pl.id, pl.name; 

your count counting rows join of tables -- rows. simple aggregation should suffice, if decide join still necessary.

edit:

you have several choices lots of columns in persons. 1 method put them in group by:

select pl.id, pl.name, p.col1, p.col2, count(*) persons p join      personline pl      on p.id = pl.id group pl.id, pl.name, p.col1, p.col2 

another method aggregation before join:

select p.*, pl.cnt person p join      (select pl.id, pl.name, count(*) cnt       personline pl       group pl.id, pl.name      ) pl      on p.id = pl.id; 

or, correlated subquery:

select p.*, (select count(*) personline pl p.id = pl.id) person;