Query's WHERE statement as a @variable

Question

I have a query which I want to insert a variable into a WHERE statement.

WHERE @Variable

I've tried the following (simplified) but it doesn't seem to work.

NOTE: I haven't included the concatenation element here trying to figure that part out myself before asking the question.

SET @id := x;
SET @n := (SELECT COUNT(*) FROM Table2 WHERE id=@id);
SET @Variable := (
(
Table1.Column1=(SELECT Column1 FROM Table2 WHERE id=@id LIMIT 1 OFFSET 0)
AND Table1.Column2=(SELECT Column2 FROM Table2 WHERE id=@id LIMIT 1 OFFSET 0)
AND Table1.Column3=(SELECT Column3 FROM Table2 WHERE id=@id LIMIT 1 OFFSET 0)
)
.........
OR
(
Table1.Column1=(SELECT Column1 FROM Table 2 WHERE id=@id LIMIT 1 OFFSET @n)
AND Table1.Column2=(SELECT Column2 FROM Table2 WHERE id=@id LIMIT 1 OFFSET @n)
AND Table1.Column3=(SELECT Column3 FROM Table2 WHERE id=@id LIMIT 1 OFFSET @n)
)
)
;


SELECT Table1.Column1, Table1.Column2, Table1.Column3, Table2.Column1, Table2.Column2, Table2.Column3 
FROM Table1, Table2
WHERE
@Variable
;

To create a dynamic query and execute it, use something like http://stackoverflow.com/questions/23178816/mysql-dynamic-query-in-stored-procedure and http://stackoverflow.com/questions/8549619/mysql-dynamically-build-query-string-in-a-stored-procedure-based-on-logic. `where @var` will not work. `where field=@var` will work in the manner you are writing your query. — zedfoxus, Jan 27 '17 at 14:39
Thanks zedfoxus, that answers my question really, it can't be done by the way I want it to, so I need to rethink my question and how I can turn it into a Loop created statement that can be N blocks in length. TY — denski, Jan 27 '17 at 15:21
I found `ANY` which seems to do what I want it to - Compare each row in a subquery. I'll post the answer here anyway. — denski, Jan 27 '17 at 15:59
Feel free to mark your own answer as accepted to give closure to your question. — zedfoxus, Jan 27 '17 at 18:38

score 0 · Accepted Answer · answered Jan 27 '17 at 16:05

So This:

SET @IDNumber := 21;
SELECT Players.PlayerID, COUNT(*) AS Games, SUM(Games.Points) 
FROM Teams, Players, Games

WHERE 
Teams.PlayerID=Players.PlayerID
AND
Games.Game=Teams.Game
AND
Games.Team=Teams.Team
AND
Games.GameDate=Teams.GameDate
AND
(
Games.Game= ANY (SELECT Game FROM Teams WHERE PlayerID=@IDNumber)
AND
Games.Team= ANY (SELECT Team FROM Teams WHERE PlayerID=@IDNumber)
AND
Games.GameDate= ANY (SELECT GameDate FROM Teams WHERE PlayerID=@IDNumber)
)

GROUP BY Teams.PlayerID
ORDER BY Games DESC
;

Creates This:

+----------+-------+-------------------+
| PlayerID | Games | SUM(Games.Points) |
+----------+-------+-------------------+
|       15 |     8 |                10 |
|       21 |     8 |                10 |
|        8 |     8 |                10 |
|       14 |     6 |                 7 |
|        5 |     6 |                 7 |
|       19 |     5 |                 6 |
|       11 |     5 |                 7 |
|       12 |     3 |                 4 |
|       10 |     3 |                 3 |
|        4 |     2 |                 3 |
+----------+-------+-------------------+
10 rows in set (0.01 sec)

Which is exactly what I was looking for.

Query's WHERE statement as a @variable

1 Answers1