#foreach ($currentEnt in $advancedCriteria.arrEnt) ( SELECT DISTINCT rvi_today.drug, rvi_today.ind, rvi_today.company, rvi_today.entfieldskey, rvi_today.phaserank, #if ($advancedCriteria.company) drug_ind_company.q5company as q5, #elseif ($advancedCriteria.target) drug_ind_target.q5target as q5, #elseif ($advancedCriteria.drug) rvi_today.q5drug as q5, #else rvi_today.q5ind as q5, #end rvi_today.devdrindphase, rvi_today.ravi FROM relaybdlive.rvi_today rvi_today #if ($advancedCriteria.target) INNER JOIN relaybdlive.drug_ind_target drug_ind_target ON (rvi_today.drug = drug_ind_target.drug) AND (rvi_today.ind = drug_ind_target.ind) #end #if ($advancedCriteria.company) INNER JOIN relaybdlive.drug_ind_company drug_ind_company ON (rvi_today.drug = drug_ind_company.drug) AND (rvi_today.ind = drug_ind_company.ind) #end WHERE TRUE #if ($advancedCriteria.searchType == "company") AND (drug_ind_company.company = $advancedCriteria.company) #else #if ($advancedCriteria.filterCompany) AND (drug_ind_company.company like concat('%', $advancedCriteria.filterCompany, '%')) #end #end #if ($advancedCriteria.searchType == "target") AND (drug_ind_target.target = $advancedCriteria.target) #end #if ($advancedCriteria.searchType == "ind") AND (rvi_today.ind = $advancedCriteria.ind) #else #if ($advancedCriteria.filterInd) AND (rvi_today.ind like concat('%', $advancedCriteria.filterInd, '%')) #end #end #if ($advancedCriteria.searchType == "drug") AND (rvi_today.drug = $advancedCriteria.drug) #else #if ($advancedCriteria.filterDrug) AND (rvi_today.drug like concat('%', $advancedCriteria.filterDrug, '%')) #end #end ORDER BY rvi_today.phaserank DESC, rvi_today.ravi ) #end SELECT DISTINCT rvi_today.drug, rvi_today.ind, drug_ind_company.company, rvi_today.entfieldskey, rvi_today.phaserank, #if ($advancedCriteria.company) drug_ind_company.q5company as q5, #elseif ($advancedCriteria.target) drug_ind_target.q5target as q5, #elseif ($advancedCriteria.drug) rvi_today.q5drug as q5, #else rvi_today.q5ind as q5, #end rvi_today.devdrindphase, rvi_today.ravi FROM relaybdlive.rvi_today rvi_today #if ($advancedCriteria.target) INNER JOIN relaybdlive.drug_ind_target drug_ind_target ON (rvi_today.drug = drug_ind_target.drug) AND (rvi_today.ind = drug_ind_target.ind) #end INNER JOIN relaybdlive.drug_ind_company drug_ind_company ON (rvi_today.drug = drug_ind_company.drug) AND (rvi_today.ind = drug_ind_company.ind) WHERE TRUE #if ($advancedCriteria.searchType == "company") AND (drug_ind_company.company = $advancedCriteria.company) #else #if ($advancedCriteria.company) AND (drug_ind_company.company like concat('%', $advancedCriteria.company, '%')) #end #end #if ($advancedCriteria.searchType == "target") AND (drug_ind_target.target = $advancedCriteria.target) #else #if ($advancedCriteria.target) AND (drug_ind_target.target like concat('%', $advancedCriteria.target, '%')) #end #end #if ($advancedCriteria.searchType == "ind") AND (rvi_today.ind = $advancedCriteria.ind) #else #if ($advancedCriteria.ind) AND (rvi_today.ind like concat('%', $advancedCriteria.ind, '%')) #end #end #if ($advancedCriteria.searchType == "drug") AND (rvi_today.drug = $advancedCriteria.drug) #else #if ($advancedCriteria.drug) AND (rvi_today.drug like concat('%', $advancedCriteria.drug, '%')) #end #end ORDER BY rvi_today.phaserank DESC, rvi_today.ravi SELECT DISTINCT X.drug, X.ind, X.company, X.entfieldskey, X.phaserank, X.q5, X.devdrindphase, X.ravi FROM ( ( SELECT P.drug, P.ind, P.company, P.entfieldskey, P.phaserank, P.q5, P.devdrindphase, P.ravi FROM rvi_today as P WHERE (company = $advancedCriteria.company) ) UNION DISTINCT ( SELECT Q.drug, Q.ind, Q.company, Q.entfieldskey, Q.phaserank, CC.q5company as q5, Q.devdrindphase, Q.ravi FROM rvi_today as Q INNER JOIN drug_ind_company as CC ON (Q.drug = CC.drug) AND (Q.ind = CC.ind) AND (Q.drug != "-Unconfirmed-") AND (Q.ind != "-Unconfirmed-") WHERE (CC.company = $advancedCriteria.company) ) ) as X WHERE TRUE #if ($advancedCriteria.searchType == "company") AND (X.company = $advancedCriteria.company) #else #if ($advancedCriteria.company) AND (X.company like concat('%', $advancedCriteria.company, '%')) #end #end #if ($advancedCriteria.searchType == "ind") AND (X.ind = $advancedCriteria.ind) #else #if ($advancedCriteria.ind) AND (X.ind like concat('%', $advancedCriteria.ind, '%')) #end #end #if ($advancedCriteria.searchType == "drug") AND (X.drug = $advancedCriteria.drug) #else #if ($advancedCriteria.drug) AND (X.drug like concat('%', $advancedCriteria.drug, '%')) #end #end ORDER BY X.phaserank DESC, X.ravi