[SOLVED] Query to join and count rows in mysql

Query to join and count rows in mysql

I have two table portal and login table.How to get count of portal_id in login table and join with portal table. If there is no matching row exists in login table show as null value

    $this->db->select("a.name");
    $this->db->from("{$this->Portal} a");
    $this->db->join("{$this->login} b","a.id = b.portal_id");
    $this->db->order_by("a.portal_id asc");

Table portal

id  |  name
1   |  john
2   |  steve
3   |  ricky
4   |  richard

Table Login

portal_id | city
1         | Bangalore
2         | Ludhiana 
1         | Chandighara
2         | Delhi

Result Table

id    |  name | count
1     | john  | 2
2     | steve | 2
3     | ricky | null

Solution

Simple left join needs to be used, to get the counts as null instead of zero you can use nullif

    select p.id,
    p.name,
    NULLIF(count(l.portal_id), 0) as 
    portal_logn_count
    from portal p left join login l on p.id = 
    l.portal_id
    group by p.id,p.name
    order by p.id,p.name