Mysql query not working for me Codeigniter Php

Question

I am working with mysql with codeigniter, hHere is my table structure:

Table A:

id(doctorid)            name    
-----------------------------
1                       abc
2                       xyz
3                       ahd
4                       djd

Table B:

id          doctor_id       type
-------------------------------------
1           1               Nuter
2           3               Spay

Now I want to get all records of doctor with count of type, I want the following result:

id          name        Nuter     Spay
---------------------------------------
1           abc         1         0
2           xyz         0         Spay

I tried with following code but not working for me, how can I do this ?

$this->db->select('A.*');
$this->db->from('A');
$this->db->join('B', 'B.doctor_id = (SELECT COUNT(Type) FROM B )');
$query = $this->db->get();

The join condition seems to be wrong.

aventurin
– aventurin

2019-03-03 10:06:52 +00:00
Commented Mar 3, 2019 at 10:06 — aventurin
– aventurin, Commented Mar 3, 2019 at 10:06

Sultan · Accepted Answer · 2019-03-03 10:07:53Z

1

Try this code:

$this->db->select('A.*,B.type');
$this->db->from('A');
$this->db->join('B', 'B.doctor_id = A.id');
$query = $this->db->get();

answered Mar 3, 2019 at 10:07

Sultan

6676 silver badges14 bronze badges

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

Comments

GMB · Accepted Answer · 2019-03-03 11:49:36Z

It seems like you are looking for conditional aggregation.

In pure SQL, the query should look like:

SELECT
    A.id,
    A.name,
    SUM(CASE WHEN B.type = 'Nuter' THEN 1 ELSE 0 END) as Nuter,
    SUM(CASE WHEN B.type = 'Spay' THEN 1 ELSE 0 END) as Spay
FROM A
INNER JOIN B ON B.doctor_id = A.id
GROUP BY A.id, A.name

If you have a 1-1 relationship between tables A and B instead of 1-N (as your sample data suggests), then this can be simplified as:

SELECT
    A.id,
    A.name,
    CASE WHEN B.type = 'Nuter' THEN 1 ELSE 0 END as Nuter,
    CASE WHEN B.type = 'Spay' THEN 1 ELSE 0 END as Spay
FROM A
INNER JOIN B ON B.doctor_id = A.id

Try:

$this->db->select(
    "A.id,
    A.name,
    CASE WHEN B.type = 'Nuter' THEN 1 ELSE 0 END as Nuter,
    CASE WHEN B.type = 'Spay' THEN 1 ELSE 0 END as Spay"
);
$this->db->from('A');
$this->db->join('B', 'B.doctor_id = A.id');
$query = $this->db->get();

Collectives™ on Stack Overflow

Mysql query not working for me Codeigniter Php

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related