Browse through lawyer profiles near you and get the legal help you need.
Join our Mailing List to Receive Marketing Tips
Showing 1 - 9 of
443 Results
SELECT DISTINCT ud.user_id, ud.listing_type, ud.years, ud.language, CASE WHEN ud.listing_type = 'individual' THEN CASE WHEN TRIM(ud.first_name) <> '' THEN CONCAT(TRIM(ud.first_name), ' ', TRIM(ud.last_name)) ELSE ud.company END ELSE CASE WHEN ud.company = '' THEN CONCAT(TRIM(ud.first_name), ' ', TRIM(ud.last_name)) ELSE ud.company END END AS name , user_service_areas.nelat, user_service_areas.nelng, user_service_areas.swlat, user_service_areas.swlng, user_service_areas.lat, user_service_areas.lng, user_service_areas.address, ud.lon as user_lng, ud.lat as user_lat FROM `users_data` AS ud INNER JOIN `subscription_types` AS st ON ud.subscription_id = st.subscription_id INNER JOIN (
SELECT
rss.service_id AS rel_service_id,
rss.user_id AS rel_service_user_id
FROM
`rel_services` AS rss
WHERE
rss.service_id IN (56)
GROUP BY
rss.user_id
) AS rs ON ud.user_id = rs.rel_service_user_id LEFT JOIN (
SELECT
sa2.user_id AS service_area_user_id,
sa2.nelat,
sa2.nelng,
sa2.swlat,
sa2.swlng,
sa2.lat,
sa2.lon as lng,
sa2.address
FROM
`service_areas` AS sa2
WHERE
(
(
(
(
41.187053 between sa2.swlat AND sa2.nelat
OR
42.88679 between sa2.swlat AND sa2.nelat
)
AND
(
-73.5081419 between sa2.swlng AND sa2.nelng
OR
-69.858861 between sa2.swlng AND sa2.nelng
)
)
OR
(
(
sa2.swlat between 41.187053 AND 42.88679
OR
sa2.nelat between 41.187053 AND 42.88679
)
AND
(
sa2.swlng between -73.5081419 AND -69.858861
OR
sa2.nelng between -73.5081419 AND -69.858861
)
)
)
AND country_sn = 'US' AND state_sn = 'MA'
)
OR ( ( sa2.country_sn = 'US' AND sa2.location_type = 'Country' ) OR ( sa2.country_sn = 'US' AND sa2.state_sn = 'MA' AND sa2.location_type = 'State' ) )
GROUP BY
sa2.user_id
) user_service_areas ON user_service_areas.service_area_user_id = ud.user_id WHERE ud.active = '2' AND st.searchable = '1' AND (rs.rel_service_id IN (56) OR st.service_limit = 'all') AND ud.state_code = "MA" AND st.search_membership_permissions REGEXP 'visitor' AND (
(
(
(
ud.lat between 41.187053 AND 42.88679
AND
ud.lon between -73.5081419 AND -69.858861
)
AND ud.country_code = 'US' AND ud.state_code = "MA"
)
OR
user_service_areas.service_area_user_id > 0
)
OR
ud.nationwide = '1'
OR
st.location_limit = 'all'
) ORDER BY st.search_priority ASC, name ASC, ud.user_id DESC
