How do I add an additional condition to a WHERE Clause in a SQL Query?_问答_开发者

I have a following SQL query where results are returned if a first name or last name of a fighter is entered. I need to add to this SQL query to return results if the first name and last name are also entered. Here is the query:

SELECT distinct
'${searchT}' searchalp,
(SELECT COUNT(fighterId) FROM tblFighter where fighter_lastName like '${searchT}%' or fighter_firstName like '${searchT}%'  ) totalresults,
A.FighterID, 
A.Fighter_FirstName, 
A.Fighter_NickName, 
if (A.Fighter_Height > 0, 
CONCAT(FLOOR(A.Fighter_Height/12), '\' ', MOD(A.Fighter_Height,12), '"'), '') Fighter_Height_inch, 
A.Fighter_NormalWeight, 
if (A.Fighter_Reach > 0, CONCAT(ROUND(A.Fighter_Reach,0), '"') , '') Fighter_Reach, 
A.Fighter_Stance,
CONCAT('/fighter/',REPLACE(REPLACE(LCASE(A.Fighter_FirstName),' ','-'),'.',''),'/',REPLACE(LCASE(A.Fighter_LastName),' ','-'),'/',A.FighterID,'/') fighterURL,
(select count(*) from tblFight_Fighter where FighterID = A.FighterID and OutcomeID  = 1) fighterCareerWins, (select count(*) from tblFight_Fighter where FighterID = A.FighterID and OutcomeID  = 2) fighterCareerLosses, (select count(*) from tblFight_Fighter w开发者_如何学JAVAhere FighterID = A.FighterID and OutcomeID  = 3) fighterCareerDraws,
fighter_lastName, FA.AccoladeDescription FROM tblFighter as A LEFT JOIN (SELECT FighterId, AccoladeDescription, AccoladeType
  FROM tblAccolade 
  JOIN tblEvent USING(EventID)
  JOIN (SELECT AccoladeID, Max(Event_Date) as MaxAccDate 
          FROM tblAccolade 
          JOIN tblEvent ON tblAccolade.EventID = tblEvent.EventID  
         WHERE AccoladeType = 'Belt' AND AccoladeDescription not like 'WEC%'
        GROUP BY AccoladeID) as tmpMaxAccDate ON tblAccolade.AccoladeID = tmpMaxAccDate.AccoladeID 
 WHERE Event_Date = MaxAccDate) as FA ON FA.FighterId = A.FighterId and FA.AccoladeType = 'Belt'  where A.fighter_lastName like '${searchT}%' or A.fighter_firstName like '${searchT}%' order by A.fighter_lastName ASC LIMIT ${stInd}, ${lstInd}

Here is an example of what is returned when a last name is entered into the input box:

<table class="wide">
<thead>
<tr>
<th>FIRST</th>
<th>LAST</th>
<th>NICKNAME</th>

<th>Ht.</th>
<th>Wt.</th>
<th>Reach</th>
<th>Stnc.</th>
<th>W</th>
<th>L</th>
<th>D</th>
<th>Belt</th>
</tr>

</thead>
<tbody><tr>
<td><a href="/fighter/scott/smith/376/">Scott</a></td>
<td><a href="/fighter/scott/smith/376/">Smith</a></td>
<td><a href="/fighter/scott/smith/376/">Hands of Steel</a></td>
<td>6' 0"</td>
<td>185 lbs</td>
<td>70"</td>
<td>Orthodox</td>
<td>4</td>

<td>6</td>
<td>0</td>
<td style="width:100px;"></td>
</tr><tr>
<td><a href="/fighter/dillon/smith/1406/">Dillon</a></td>
<td><a href="/fighter/dillon/smith/1406/">Smith</a></td>
<td><a href="/fighter/dillon/smith/1406/"></a></td>
<td>6' 0"</td>
<td>170 lbs</td>
<td></td>
<td></td>

<td>1</td>
<td>0</td>
<td>0</td>
<td style="width:100px;"></td>
</tr><tr>
<td><a href="/fighter/maurice/smith/19/">Maurice</a></td>
<td><a href="/fighter/maurice/smith/19/">Smith</a></td>
<td><a href="/fighter/maurice/smith/19/"></a></td>
<td>6' 2"</td>
<td>221 lbs</td>

<td></td>
<td>Orthodox</td>
<td>6</td>
<td>3</td>
<td>0</td>
<td style="width:100px;"></td>
</tr><tr>
<td><a href="/fighter/david/smith/1236/">David</a></td>
<td><a href="/fighter/david/smith/1236/">Smith</a></td>
<td><a href="/fighter/david/smith/1236/"></a></td>
<td></td>

<td>155 lbs</td>
<td></td>
<td></td>
<td>2</td>
<td>1</td>
<td>0</td>
<td style="width:100px;"></td>
</tr><tr>
<td><a href="/fighter/eric/smith/1027/">Eric</a></td>
<td><a href="/fighter/eric/smith/1027/">Smith</a></td>
<td><a href="/fighter/eric/smith/1027/"></a></td>

<td></td>
<td>0 lbs</td>
<td></td>
<td>Southpaw</td>
<td>0</td>
<td>1</td>
<td>0</td>
<td style="width:100px;"></td>
</tr><tr>
<td><a href="/fighter/patrick/smith/48/">Patrick</a></td>
<td><a href="/fighter/patrick/smith/48/">Smith</a></td>

<td><a href="/fighter/patrick/smith/48/"></a></td>
<td>6' 2"</td>
<td>225 lbs</td>
<td></td>
<td>Orthodox</td>
<td>4</td>
<td>2</td>
<td>0</td>
<td style="width:100px;"></td>
</tr><tr>
<td><a href="/fighter/adam/smith/1269/">Adam</a></td>

<td><a href="/fighter/adam/smith/1269/">Smith</a></td>
<td><a href="/fighter/adam/smith/1269/"></a></td>
<td></td>
<td>0 lbs</td>
<td></td>
<td></td>
<td>0</td>
<td>1</td>
<td>0</td>
<td style="width:100px;"></td>
</tr><tr>
<td><a href="/fighter/josh/smith/1121/">Josh</a></td>

<td><a href="/fighter/josh/smith/1121/">Smith</a></td>
<td><a href="/fighter/josh/smith/1121/"></a></td>
<td>6' 0"</td>
<td>155 lbs</td>
<td></td>
<td>Orthodox</td>
<td>0</td>
<td>1</td>
<td>0</td>
<td style="width:100px;"></td>

</tr></tbody>
</table>

In the final line simply change:

where A.fighter_lastName like '${searchT}%' or A.fighter_firstName like '${searchT}%'

to use AND instead of OR

Modify line three above to have an AND in it:

(SELECT COUNT(fighterId) FROM tblFighter where fighter_lastName like '${searchT}%' AND fighter_firstName like '${searchT}%'  ) totalresults,

That should fix you problem.

Edit: If you want to modify your query to accept two names in one field "John Doe" (thus you would search for a first name of John and a last name of Doe), you will need to split the string on the space and do a different WHERE clause. You could try to pu some logic in the existing WHERE clause or you could call a different query. However, as it has been pointed out, you are doing way too much business logic inside your SQL statement. Try bringing this processing into your code's business layer. For example, the one name verses two names you are describing could be determined much more easily in your code. Then you could modify the call accordingly.