CODEWITHSUNDEEP
phpmysqljoingroup-by
user_777
user_777

Reputation: 805

Join two tables with group by condition

Here i want to join two table and want to produce the output like

Akbar Travels building      2018-10
Thrikandiyur Quarters       2018-10


Akbar Travels building     2018-11
Thrikandiyur Quarters      2018-11

Here i have two tables name table A,table B,table A structure looks like this 

category_id              category_name  
    5               Akbar Travels building          
    6               Thrikandiyur Quarters

table B structure looks like this 

id     paying_month     parent_category     
1         2018-10           5   
2         2018-10           5   
3         2018-11           5   
4         2018-11           5   
5         2018-10           6

Here for getting output i wrote my code code like this

public function get_date_wise_pdf_report($from, $to)
{
       $query=$this->db->query("SELECT * from tableA A left join tableB B  on A.category_id = B.parent_category and B.paying_month BETWEEN '{$from}' AND '{$to}' ");
       return $query->result();
 }

but when i use this am getting like this 

Akbar Travels building     2018-10
Akbar Travels building     2018-10
Akbar Travels building     2018-11
Akbar Travels building     2018-11
Thrikandiyur Quarters      2018-10

Am unable to give group by statement here and if i gave only two rows will be the out come,please help me to solve

Upvotes: 5

Views: 166

Answers (2)

Vinícius Britto
Vinícius Britto

Reputation: 320

According to your tables, "Thrikandiyur Quarters" makes a relation only to "2018-10". So, to your output have this row: "Thrikandiyur Quarters 2018-11" you should have a reference in your table b like this:

id  ||   paying_month  ||   parent_category  
6   ||    2018-11      ||    6

The Condition that you're using is just a between dates. To get a output not repeated, you should put a "DISTINCT" in your statement. Like this:

$query=$this->db->query("SELECT DISTINCT * from tableA A left join tableB B  on 
A.category_id = B.parent_category and B.paying_month BETWEEN '{$from}' AND 
'{$to}' ");

Another solution is use a "GROUP BY":

$query=$this->db->query("SELECT * from tableA A left join tableB B  on 
A.category_id = B.parent_category and B.paying_month BETWEEN '{$from}' AND 
'{$to}' GROUP BY B.parent_category");

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Use distinct

SELECT distinct A.category_name,B.paying_month
from tableA A left join tableB B  on A.category_id = B.parent_category and 
B.paying_month BETWEEN '{$from}' AND '{$to}'
order by B.paying_month

Upvotes: 4

Related Questions