Using Extra WHERE in SQL INNER JOIN

Question

$sql = "SELECT events.*, leagues.lname as leagueName,leagues.aliasname as AliasName, t1.tname as team1_name, t2.tname as team2_name FROM events INNER JOIN leagues ON events.leagueid = leagues.id INNER JOIN teams AS t1 ON events.teamhid = t1.teamid INNER JOIN teams AS t2 ON events.teamaid = t2.teamid WHERE sdate='1352835000' "; 

It gives a TABLE with COLUMNS:

 id sdate ... leagueName AliasName team1_name team2_name 

How can I add additional conditions:

 WHERE AliasName LIKE 'word' AND (team1_name LIKE 'word2' OR team2_name LIKE 'word3') 

Answer 1

you cannot use the ALIAS column in (in a row) with the where clause, so in your case you need to use tableName.columnName (except wrapping it in a subquery)

 WHERE leagues.aliasname LIKE 'word' AND (t1.tname LIKE 'word2' OR t2.tname LIKE 'word3') 

the reason you cannot use is because the order of the query is as follows

• FROM (you can set the alias TABLE here)
• WHERE clause
• GROUP BY clause
• HAVING offer
• SELECT clause (alias COLUMN)
• ORDER BY clause

Answer 2

Same:

 SELECT * FROM ( SELECT events.*, leagues.lname as leagueName, leagues.aliasname as AliasName, t1.tname as team1_name, t2.tname as team2_name FROM events INNER JOIN leagues ON events.leagueid = leagues.id INNER JOIN teams AS t1 ON events.teamhid = t1.teamid INNER JOIN teams AS t2 ON events.teamaid = t2.teamid WHERE sdate = '1352835000' ) t WHERE AliasName LIKE 'word' AND (team1_name LIKE 'word2' OR team2_name LIKE 'word3')