naeemqaswar
10/6/2017 - 10:15 AM

Capsulate Sub-query Data in Group

This non-generic query retrieves the collection of encapsulated data (by HTML) from a sub query. Each sub record is covered with html element and separating record with others by html element known as "hr"

cap_sub_query_data_in_group.sql
content_copyfile_download
Select d.id, d.name, 
    (
    	select concat(u.first_name,' ',u.last_name) as supplier_name from user u where u.id = d.provider_id
    ) as supplier, d.name, 
    (
    	select concat(u.first_name,' ',u.last_name) as host_name from user u where u.id = d.created_by
    ) as host, 
    GROUP_CONCAT((
      select concat(u2.first_name,' ',u2.last_name,'<a class="remove-adder" id="',u2.id,'"><i class="fa fa-times-circle"></i></a>') as added_by_users from user u2 where u2.id = ud.user_id
    ) SEPARATOR '<hr>') as added_by, 
    d.created
from deal d
left join user_deals ud
on ud.deal_id = d.id
group by d.id
order by d.id desc