1. Home
  2. Attorneys & Legal Services
    3. Back to Results

APPLY FILTERS:

SELECT p.*,u.group_id,g.top_search_result FROM `ad_product` as p INNER JOIN `ad_user` as u ON u.id = p.user_id INNER JOIN `ad_usergroups` as g ON g.group_id = u.group_id where p.status = 'active' AND p.hide = '0' AND (p.sub_category = '64') AND (p.country = 'US') AND p.id NOT IN (SELECT DISTINCT p.id FROM `ad_product` AS p WHERE p.status = 'active' AND p.hide = '0' AND p.promoted = 1 AND FROM_UNIXTIME(p.promoted_date) >= CURRENT_DATE AND sub_category = '64' ) ORDER BY (CASE WHEN g.top_search_result = 'yes' and p.featured = '1' and p.urgent = '1' and p.highlight = '1' THEN 1 WHEN g.top_search_result = 'yes' and p.urgent = '1' and p.featured = '1' THEN 2 WHEN g.top_search_result = 'yes' and p.urgent = '1' and p.highlight = '1' THEN 3 WHEN g.top_search_result = 'yes' and p.featured = '1' and p.highlight = '1' THEN 4 WHEN g.top_search_result = 'yes' and p.urgent = '1' THEN 5 WHEN g.top_search_result = 'yes' and p.featured = '1' THEN 6 WHEN g.top_search_result = 'yes' and p.highlight = '1' THEN 7 WHEN g.top_search_result = 'yes' THEN 8 WHEN p.featured = '1' and p.urgent = '1' and p.highlight = '1' THEN 9 WHEN p.urgent = '1' and p.featured = '1' THEN 10 WHEN p.urgent = '1' and p.highlight = '1' THEN 11 WHEN p.featured = '1' and p.highlight = '1' THEN 12 WHEN p.urgent = '1' THEN 13 WHEN p.featured = '1' THEN 14 WHEN p.highlight = '1' THEN 15 ELSE 16 END), id ASC LIMIT 0,20
SELECT DISTINCT p.* FROM `ad_product` AS p WHERE p.status = 'active' AND p.product_name LIKE '%%' AND p.hide = '0' AND p.promoted = 1 AND FROM_UNIXTIME(p.promoted_date) >= CURRENT_DATE AND sub_category = '64' ORDER BY p.id DESC LIMIT 5