I have the following MySQL query (failed attempt):
SELECT * FROM
(SELECT @rownum:=@rownum+1 rank, id, userID, total
FROM table1 total, (SELECT @rownum:=0) r
WHERE id = 318467989655781389 && mydb.table2.colm = 'false'
ORDER BY total DESC) a
LIMIT 10
However, I get an error saying that mydb.table2.colm isn't in the field list. How can I get data from another table to use in a WHERE clause?
You would use a JOIN. The query would look something like this:
SELECT (@rownum := @rownum + 1) as rank, t1.id, t1.userID, t1.total
FROM table1 t1 JOIN
mydb.table2 t2
ON t1.? = t2.? CROSS JOIN
(SELECT @rownum := 0) params
WHERE t1.id = 318467989655781389 AND t1.colm = 'false'
ORDER BY t1.total DESC
LIMIT 10;
The ? is for the columns used for joining the tables together.
Notes:
FROM clause.LIMIT. In some versions of MySQL, a subquery might be necessary for the ORDER BY, but the query would look different.AND operator in SQL is AND. Although && works in MySQL, you might as well use the standard operator.Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments