Using additional 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 TABLE with COLUMNS:

id sdate ... leagueName AliasName team1_name team2_name

How could I add additional conditions:

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

John Woo · Accepted Answer · 2012-11-13 15:03:20Z

2

you can't use ALIAS of column on (which is in-line with) where clause, so in your case you need to use the tableName.columnName (aside from wrapping it in a subquery)

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

the reason why you can't use is because the order of execution of the query is as follows

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

edited Nov 13, 2012 at 15:03

answered Nov 13, 2012 at 14:35

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mahmoud Gamal · Accepted Answer · 2012-11-13 14:35:11Z

1

Like so:

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')

answered Nov 13, 2012 at 14:35

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Collectives™ on Stack Overflow

Using additional WHERE in SQL INNER JOIN

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related