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

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         jose@yahoo.in      457855654
002         Tom          tom@yahoo.in       5464644654
003         Nick         nick@yahoo.in      4545645644
004         Rose         rose@yahoo.in      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        jose@yahoo.in      457855654           2                   1
Tom         tom@yahoo.in       5464644654          0                   1
Nick        nick@yahoo.in      4545645644          2                   1
Rose        rose@yahoo.in      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?

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        jose@yahoo.in   457855654   2               1
Tom         tom@yahoo.in    5464644654  0               1
Nick        nick@yahoo.in   4545645644  2               1
Rose        rose@yahoo.in   554545441   1               2

SQLFiddle Demo