meow meow - 21 days ago 7
PHP Question

how to combine two queries to produce one result in codigniter

this is my query working fine

$q = "select distinct attandence.user_id ,monthname(date), sum(is_absent) as absents,in_time,out_time , count(date) as working_days, leave_type , users.f_name ,leave_types.type ,leave_types.leave_type_id , users.l_name from attandence join users on (users.user_id = attandence.user_id) left join leave_types on(leave_types.leave_type_id = attandence.leave_type) where in_time != '' and out_time != '' ".$where_condition." GROUP BY attandence.user_id LIMIT ".$limit." OFFSET ". $offset;


now i also want

$q = 'select count(leave_type) from attandence where leave_type != 0 and user_id = 15 and month(date) = 10';


here everything is given static
i want the first query work same as working now but also use count('leave_type') where leave+type != 0;

how this can be used in first query

if i am using

$q = "select distinct attandence.user_id , count(leave_type) as total ,monthname(date) , sum(is_absent) as absents,in_time,out_time , count(date) as working_days , users.f_name ,leave_types.type ,leave_types.leave_type_id , users.l_name from attandence join users on (users.user_id = attandence.user_id) left join leave_types on(leave_types.leave_type_id = attandence.leave_type) where in_time != '' and out_time != '' ".$where_condition." GROUP BY attandence.user_id LIMIT ".$limit." OFFSET ". $offset;

display same on total and working days

please provide solution

Any help is going to be appreciated. :)

Answer

I have solution your query you can add sub query Sub query

See Example:

$q = "select distinct attandence.user_id ,monthname(date), sum(is_absent) as absents,in_time,out_time , count(date)  as working_days, leave_type , users.f_name ,leave_types.type ,leave_types.leave_type_id , users.l_name, (select count(leave_type) from attandence where leave_type != 0 and user_id = 15 and month(date) = 10) as leave_count  from attandence join users on (users.user_id = attandence.user_id) left join leave_types on(leave_types.leave_type_id = attandence.leave_type) where in_time != '' and out_time != ''  ".$where_condition." GROUP BY attandence.user_id LIMIT ".$limit." OFFSET ". $offset;