Anders Norrbring skrev:
Brent,
you put me on the right track.. ;) Reading up a bit on syntax for
variables, I came up with the following, which seems to work.
SET @tid:=(SELECT tid FROM objects WHERE shortname = %s);
SET @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s);
SELECT (count(*
You could use a view:
CREATE VIEW all_scores
SELECT s.tid, s.vid, s.uid, s.highScore
FROM score s
JOIN objects o
ON s.tid = o.tid
JOIN itemtypes it
ON s.vid = it.vid
JOIN users u
ON s.uid = u.uid
WHERE o.shortname = %s /* Should these ANDs really be ORs? */
AND i.itemtype LIKE %s;
SELECT highSco
Brent Baisley skrev:
You're right that wouldn't work because you can't assign the query
results to a variable. You want to put the variables in the query and
assign them to the value of the field.
...WHERE s2.tid = (SELECT @tid:=tid AS tid FROM objects WHERE shortname = %s)...
That should work b
Brent,
you put me on the right track.. ;) Reading up a bit on syntax for
variables, I came up with the following, which seems to work.
SET @tid:=(SELECT tid FROM objects WHERE shortname = %s);
SET @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s);
SELECT (count(*)+)/(SELECT COUNT(*) FROM
Brent Baisley skrev:
You might be able to use variables to store the result of the query.
Although I've never tried assigning the result of a query to a
variable, only field values.
SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname
Moon's Father skrev:
Just a look at your sql query at first.
Your like key word's right must like this:
like 's%', then it'll use the index you created for your table.
The second try you may use temporary table to replace your own query like "
(SELECT uid FROM users WHERE username = %s)
"
Well
Hi.. I'm struggling with a query that I'm trying to simplify as much as
possible, but I can't seem to get rid of using the very same subqueries
several times.
Would there be a way to optimize the following so I get rid of
subqueries that do the exact same thing more than once?
SELECT (count(*