A. Sharma A. Sharma - 16 days ago 9
MySQL Question

mysql query with join clause and group by clause

I want this as result

------------------------------------------------------
c_name | c_id | email_count | number_count |
-----------------------------------------------------
course1 | cid1234 | 7 |7 |
course10 | cid7489 | 4 |4 |
course9 | cid0987 | 2 |2 |
course5 | cid2321 | 2 |2 |
course6 | cid8905 | 1 |1 |
course2 | cid2134 | 1 |1 |
course3 | cid4352 | 1 |1 |
course7 | cid9087 | 1 |1 |
course8 | cid6574 | 1 |1 |
course4 | cid1244 | 0 |0 |
---------------------------------------------------


but i am getting result like this

------------------------------------------------------ -----------
c_name | c_id | email_count | number_count |
-------------------------------------------------------------------
null | cid1234 | 7 |7 |
null | cid7489 | 4 |4 |
null | cid0987,cid2321 | 2 |2 |
null | cid8905,cid4352,cid6574 | 1 |1 |
null | cid2134,cid9087 | 1 |1 |
null | cid1244 | 0 |0 |
------------------------------------------------------------------


I have two databse tables name as (course_master) & (search_course) in one table course details are there, such as
1.) course name as (c_name) ,

2.) course Id as (c_id) ,

In another table in that three coulombs are there

1.) course Id as (c_id) ,

2.)course sent on email as (c_eml) ,

3.) course sent on number (c_num) ,

such as

first table name course_master
--------------------------
s.no | c_name | c_id |
--------------------------
1. | course1 | cid1234 |
2. | course2 | cid2134 |
3. | course3 | cid4352 |
4. | course4 | cid1244 |
5. | course5 | cid2321 |
6. | course6 | cid8905 |
7. | course7 | cid9087 |
8. | course8 | cid6574 |
9. | course9 | cid0987 |
10. | course10| cid7489 |
--------------------------


second Table Name is like search_course

-----------------------------------------------
s.no.| c_id | c_eml | c_num |
-----------------------------------------------
1. | cid8905 |example@host.com | 9999999999|
2. | cid7489 | any email Id | any number|
3. | cid0987 | ------"-------- | -----"----|
4. | cid1234 | ------"-------- | -----"----|
5. | cid2134 | ------"-------- | -----"----|
6. | cid4352 | ------"-------- | -----"----|
7. | cid1234 | ------"-------- | -----"----|
8. | cid1234 | ------"-------- | -----"----|
9. | cid2321 | ------"-------- | -----"----|
10. | cid2321 | ------"-------- | -----"----|
11. | cid1234 | ------"-------- | -----"----|
12. | cid9087 | ------"-------- | -----"----|
13. | cid1234 | ------"-------- | -----"----|
14. | cid1234 | ------"-------- | -----"----|
15. | cid6574 | ------"-------- | -----"----|
16. | cid1234 | ------"-------- | -----"----|
17. | cid7489 | ------"-------- | -----"----|
18. | cid0987 | ------"-------- | -----"----|
19. | cid7489 | ------"-------- | -----"----|
20. | cid7489 | ------"-------- | -----"----|


i try my level best, i even tried very many join query but i think the course ids are coming in group like for eml_count = 1 , their is 3 course Id in that row , i am unable to get result what i want (Course Name Accordingly).. soo please any one can try to write a mysql query for this it will be very great help for me thanks in advance ....

Answer

I tried like this

select 
 c_name,A.c_id,
    count(c_eml) as 'ceml',COUNT(c_num) AS CNUMBER_COUNT
from #A(table 1) A
  left join #B(table_2)  B on
 A.c_id=B.c_id
group by
    c_name,A.c_id