Query to join and count rows in mysql [duplicate]

Question

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

bi_noob · Accepted Answer · 2019-03-01 12:57:52Z

1

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

answered Mar 1, 2019 at 12:57

bi_noob

1073 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

B L Praveen Over a year ago

does this work with pagination count. where limit and offset are used

bi_noob Over a year ago

It should, I dont have a php setup ready at my end but it works at the front end that I use

B L Praveen Over a year ago

In paginatoin i need to fetch total number of records . How to fetch total number of records with group by

Paul Spiegel Over a year ago

@BLPraveen: select count(distinct id from portal).

Porta Tela Webbuilding · Accepted Answer · 2019-03-01 12:51:08Z

1

SELECT p.id, p.name, COUNT(l.id) AS `count`
FROM portal p
LEFT JOIN Login l ON l.portal_id = p.id
GROUP BY p.id

answered Mar 1, 2019 at 12:51

Porta Tela Webbuilding

214 bronze badges

Comments

Ahmed Faraz Siddiqui · Accepted Answer · 2019-03-01 12:51:35Z

1

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

Making a query like

Select a.id, a.name, count(a.id) from portal a
left join login b on a.id = b.portal_id
group by a.id
order by a.id asc

answered Mar 1, 2019 at 12:51

Ahmed Faraz Siddiqui

295 bronze badges

1 Comment

B L Praveen Over a year ago

How to get total no of records

Maruthi Prasad · Accepted Answer · 2019-03-01 13:16:20Z

1

Works Perfectly....

    Select a.id, a.name, NULLIF(count(b.portal_id ), 0) from portal a
    left join Login b on a.id = b.portal_id 
    group by a.id
    order by a.id asc

edited Mar 1, 2019 at 13:16

answered Mar 1, 2019 at 13:10

Maruthi Prasad

1701 silver badge13 bronze badges

1 Comment

B L Praveen Over a year ago

How to get the total no of records

Collectives™ on Stack Overflow

Query to join and count rows in mysql [duplicate]

4 Answers 4

4 Comments

Comments

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

1 Comment

1 Comment

Linked

Related