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

Question

I need to join 2 tables (Person and PersonLine). The result should contain id and name column from Person table and count of personlineid column from PersonLine Table for each id. But sql query returns count of all personlineid. Can anyone help to form the 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 
    FROM PERSON P 
    LEFT JOIN PERSONLINE PL 
    ON P.ID = PL.ID,
    (SELECT count(PL.PERSONLINEID) cnt FROM PERSON P LEFT JOIN PERSONLINE PL ON P.ID = PL.ID WHERE 
    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

Answer 1

With your sample data, you don't even need the Person table -- because you seem to have redundant table in the two tables. You should probably fix this, but:

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

Your count is just counting all the rows from the join of the tables -- that would be all the rows. A simple aggregation should suffice, even if you decide that the join is still necessary.

EDIT:

You have several choices with lots of columns in persons. One method is to put them in the group by:

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

Another method is to do the aggregation before the join:

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

Or, a correlated subquery:

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