CODEWITHSUNDEEP
mysqlcodeigniter
B L Praveen
B L Praveen

Reputation: 1990

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

Upvotes: 0

Views: 61

Answers (4)

Maruthi Prasad
Maruthi Prasad

Reputation: 170

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

Upvotes: 1

bi_noob
bi_noob

Reputation: 107

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

Upvotes: 1

Ahmed Faraz Siddiqui
Ahmed Faraz Siddiqui

Reputation: 29

$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

Upvotes: 1

Porta Tela Webbuilding
Porta Tela Webbuilding

Reputation: 21

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

Upvotes: 1

Related Questions