Unknown Column?

I'm trying to find mutual friends between these two users: user1 and user92

This is the SQL that is successful in displaying them:

SELECT IF(user_a = 1 OR user_a = 92, user_b, user_a) friend
            FROM friendship
            WHERE (user_a = 1 OR user_a = 92) OR (user_b = 1 OR user_b = 92)
            GROUP BY 1
            HAVING COUNT(*) > 1

This is how it looks:

friend

61

72

73

74

75

76

77

78

79

80

81

So now I want to select all users after the number 72, and I try to do it with this SQL but it's not开发者_运维技巧 working? It gives me the error, "unknown column name friend in where clause"

SELECT IF(user_a = 1 OR user_a = 92, user_b, user_a) friend
            FROM friendship
            WHERE friend > 72 
              AND (user_a = 1 OR user_a = 92) OR (user_b = 1 OR user_b = 92)
            GROUP BY 1
            HAVING COUNT(*) > 1

What am I doing wrong, or what is the correct way?

---

You have only just defined the column "friend" in the SELECT clause, so the WHERE clause cannot "see" it (it is processed before the SELECT clause).

Subquery it

SELECT * FROM (
SELECT IF(user_a = 1 OR user_a = 92, user_b, user_a) friend
FROM friendship
WHERE (user_a = 1 OR user_a = 92) OR (user_b = 1 OR user_b = 92)
) X
WHERE friend > 72
GROUP BY friend
HAVING COUNT(*) > 1

---

You can copy the IF() into the WHERE clause. Also note that you need to group the second part of the WHERE together:

SELECT IF(user_a = 1 OR user_a = 92, user_b, user_a) friend
FROM friendship
WHERE IF(user_a = 1 OR user_a = 92, user_b, user_a) > 72
    AND ((user_a = 1 OR user_a = 92) OR (user_b = 1 OR user_b = 92))
GROUP BY 1
HAVING COUNT(*) > 1

---

I'm trying to read between your lines.
Are you trying to list your result in "pages" and that is the reason you want to read from 72?

If you are trying to create pages, have a look at LIMIT foo,bar foo is the starting row number from your result set (not your ID) and bar is the number of rows to return.

LIMIT 100,20 will return 20 rows from your result set starting with row 100.