How to join a master table with 2 tables for getting count of master table primary id in 2 tables and with master table information in codeigniter

Question

I have a master table its name is "user" table it contain all information of user and there are two tables "business_list" and "classified_list".

I want to show all user information with total number of business from business_list table and total no of classifieds from classified_list table

user table

user_id    user_name     user_email         user_phone
-----------------------------------------------------     
001         Jose         [email protected]      457855654
002         Tom          [email protected]       5464644654
003         Nick         [email protected]      4545645644
004         Rose         [email protected]      554545441

business_list table

bid        user_id      business_name
-----------------------------------------------
001        001          Construction business
002        003          Event business
003        001          Crane business
004        003          Furtinure business
005        004          Realestate business

classified_list table:

cid      user_id       classified_name
-------------------------------------------    
001      001           Roller classified
002      004           Home classified
003      003           Chair classified
004      004           Office Classified
005      002           Light decoration classified

I want to display information as

User Name   User Email         User Phone     No Of Business     No Of Classified
---------------------------------------------------------------------------------
Jose        [email protected]      457855654           2                   1
Tom         [email protected]       5464644654          0                   1
Nick        [email protected]      4545645644          2                   1
Rose        [email protected]      554545441           1                   2

So what is the mysql join query for getting this result, I am using php codeigniter 3.0 framework so it is nice, if anyone knows codeigniter query for this result?

Answer 1

This query will give you the results you want. It joins the user table to a UNION of the business_list and classified_list tables and uses conditional aggregation to sum the number of businesses and classifieds associated with each user:

SELECT u.user_name AS `User Name`,
       u.user_email AS `User Email`,
       u.user_phone AS `User Phone`,
       SUM(CASE WHEN bc.type = 'business' THEN 1 ELSE 0 END) AS `No Of Business`,
       SUM(CASE WHEN bc.type = 'classified' THEN 1 ELSE 0 END) AS `No Of Classified`
FROM user u
JOIN (SELECT 'business' AS type, user_id 
      FROM business_list
      UNION ALL
      SELECT 'classified' AS type, user_id
      FROM classified_list) bc
ON bc.user_id = u.user_id
GROUP BY u.user_id;

Alternatively, you can use a LEFT JOIN to each table, COUNTing DISTINCT values from each table:

SELECT u.user_name AS `User Name`,
       u.user_email AS `User Email`,
       u.user_phone AS `User Phone`,
       COUNT(DISTINCT b.bid) AS `No Of Business`,
       COUNT(DISTINCT c.cid) AS `No Of Classified`
FROM user u
LEFT JOIN business_list b ON b.user_id = u.user_id
LEFT JOIN classified_list c ON c.user_id = u.user_id
GROUP BY u.user_id

The output of both queries is the same:

User Name   User Email      User Phone  No Of Business  No Of Classified
Jose        [email protected]   457855654   2               1
Tom         [email protected]    5464644654  0               1
Nick        [email protected]   4545645644  2               1
Rose        [email protected]   554545441   1               2

SQLFiddle Demo

Answer 2

Try something like this.

select u.user_name, u.user_email, u.user_phone, count(distinct b.bid),
       count(distinct c.cid) from user_table u 
   left outer join business_list b 
       on  u.user_id=b.user_id 
   left outer join classified_list c 
       on c.user_id=u.user_id 
group by 
    u.user_name,u.user_email,u.user_phone