SELECT DISTINCT t1.MedicId,
			t1.UserId,
			t1.ProfessionalLicense,
			t1.SpecialtyId,
			CASE
				WHEN t1.MedicTitleId = 4 THEN t5.DisplayName
				ELSE CONCAT(t7.`Name`, ' ', t5.DisplayName)
			END DisplayName,
			t6.AttachmentUrl ImageUrl,
			t5.Slug Slug,
			t1.Founder,
			t1.Verified,
			t8.`Name` Specialty,
			t8.Slug SpecialtySlug,
			t11.MembershipId,
			t11.`Name` Membership,
			(
				SELECT COUNT(MedicId)
				FROM MedicReview
				WHERE MedicId = t1.MedicId AND Active = 1 AND Hidden = 0
			) TotalReviews,
			(
				SELECT COUNT(MedicId)
				FROM HealthTip
				WHERE MedicId = t1.MedicId AND Active = 1 AND Hidden = 0
			) TotalHealthTip,
			t1.Order
		FROM Medic t1
		 INNER JOIN (
				SELECT MedicId
				FROM MedicClinic t2
				INNER JOIN Address t3 ON t3.AddressId = t2.AddressId
				INNER JOIN City t4 ON t4.CityId = t3.CityId
				WHERE t4.Slug = 'chihuahua' AND t2.Active = 1
				OR t4.MetropolitanAreaId IN (
					SELECT t1.MetropolitanAreaId
					FROM MetropolitanArea t1
					LEFT JOIN City t2 ON t2.MetropolitanAreaId = t1.MetropolitanAreaId
					WHERE t2.Slug = 'chihuahua'
				)
				GROUP BY MedicId
			) t2 ON t2.MedicId = t1.MedicId
		INNER JOIN `User` t5 ON t5.UserId = t1.UserId
		LEFT JOIN UserAttachment t6 ON t6.UserId = t5.UserId AND t6.active = 1 AND t6.UserAttachmentTypeId = 1
		INNER JOIN MedicTitle t7 ON t7.MedicTitleId = t1.MedicTitleId
		INNER JOIN Specialty t8 ON t8.SpecialtyId = t1.SpecialtyId
		INNER JOIN PaymentTransaction t9 ON t9.UserId = t1.UserId AND DATE_FORMAT(NOW(),'%Y-%m-%d') BETWEEN t9.StartDate AND t9.EndDate AND t9.PaymentStatusId = 2 AND t9.Active = 1
		LEFT JOIN MembershipPlan t10 ON t10.MembershipPlanId = t9.MembershipPlanId
		LEFT JOIN Membership t11 ON t11.MembershipId = t10.MembershipId
		WHERE t1.Hidden = 0 AND t5.Active = 1 AND t5.UserStatusId = 2 AND ( (
					LOWER(t5.DisplayName) LIKE LOWER('traumatologia')
					OR LOWER(t5.Slug) LIKE LOWER('%traumatologia')
					OR LOWER(t8.`Name`) LIKE LOWER('traumatologia')
					OR LOWER(t8.Slug) LIKE LOWER('traumatologia')
				) OR t1.MedicId IN (
					SELECT t1.MedicId
					FROM MedicSpecialty t1
					INNER JOIN Specialty t2 ON t2.SpecialtyId = t1.SpecialtyId
					WHERE (
						t2.Slug = 'traumatologia'
						OR LOWER(t2.`Name`) LIKE LOWER('traumatologia')
				)
				) OR t1.MedicId IN (
					SELECT MedicId
					FROM MedicService
					WHERE LOWER(Slug) LIKE LOWER('traumatologia')
				)
				 OR t8.SpecialtyId IN (
					SELECT DISTINCT t2.SpecialtyId
					FROM Treatment t1
					INNER JOIN TreatmentCategorySpecialty t2 ON t2.TreatmentCategoryId = t1.TreatmentCategoryId
					WHERE LOWER(Slug) LIKE LOWER('traumatologia')
				)) ORDER BY FIELD(t10.MembershipId, 4, 3, 2, 1), COALESCE(t1.Order,9999), RAND() LIMIT 0, 10;