MySQL Query to Select dependent rows from Joined tables

Question

I'm working on a codeigniter project.

Here are my tables

category
cid cname
5   general
6   science
7   math

books
bid bname
12  first
13  second
14  third
15  fourth
16  fifth
17  sixth

dir
id  bid     cid
1   12      5
2   13      6
3   14      7
4   15      6
5   16      5
6   17      5

As you can see joining the tables is easy but here is what I need to do.

Create a function that will give me category name(cname) and the number of books in that category. For example the outcome should be like

general 3
science 2
math    1

here is my WIP model

function category_details(){
        $this->db->order_by('cname','asc');
        $query=$this->db->query('Select * from dir join category on category.cid=dir.cid join books on dir.bid=books.bid');
        return $query->result_array();
}

Any help would be much appreciated.

what's output now ??

Abdulla Nilam
– Abdulla Nilam

2017-04-21 04:41:16 +00:00
Commented Apr 21, 2017 at 4:41 — Abdulla Nilam
– Abdulla Nilam, Commented Apr 21, 2017 at 4:41

Community · Accepted Answer · 2017-05-23 10:31:07Z

0

The query should be like this to get expected result

Select category.cname,count(dir.cid) from dir join category on category.cid=dir.cid join books on dir.bid=books.bid group by category.cname

And your function should be like this

function category_details(){
        $this->db->order_by('cname','asc');
        $query=$this->db->query('Select category.cname,count(dir.cid) from dir join category on category.cid=dir.cid join books on dir.bid=books.bid group by category.cname');
        return $query->result_array();
}

you can refer this code COUNT / GROUP BY with active record? for proper CI query.

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Apr 21, 2017 at 4:57

Mack4Hack

1196 bronze badges

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

Collectives™ on Stack Overflow

MySQL Query to Select dependent rows from Joined tables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related