CODEWITHSUNDEEP
mysqlsqlselect
user9375768
user9375768

Reputation:

MySQL - Selecting data from another table for a WHERE clause

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?

Upvotes: 1

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269783

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:

  • Use table aliases and qualified column names whenever a query contains more than one table.
  • You can only reference a table that has been specific in the FROM clause.
  • A subquery is not needed for the LIMIT. In some versions of MySQL, a subquery might be necessary for the ORDER BY, but the query would look different.
  • The boolean AND operator in SQL is AND. Although && works in MySQL, you might as well use the standard operator.

Upvotes: 2

Related Questions