MySQL IF() Sub Select

Question

I am having trouble with doing a SUB SELECT in an IF() condition.

I'm trying to get it to select select the MIN saved time if the type does not equal a certain value, excluding that value from the calculation.

Here is my current SQL query that works except for the IF():

SELECT
t.episode,
t.anime_id,
MAX(t.type) as type,
MIN(t.HD) as HD,
MAX(t.thumbnail) as thumbnail,
IF((MAX(t.type)!="raw"),(SELECT MIN(e.saved) FROM anime_episodes e WHERE e.type=MAX(t.type) AND t.episode=e.episode AND t.anime_id=e.anime_id GROUP BY e.anime_id, e.episode), (MIN(t.saved))) as time,
// UP HERE ^
MAX(t.filler) as filler,
m.anime_name,
m.furl,
m.video_thumb
FROM anime_episodes t
LEFT JOIN anime_list m ON
( t.anime_id=m.anime_id )
WHERE t.approved=1 AND t.locked=0 AND m.status=0 AND t.episode>=m.latest_ep
GROUP BY t.anime_id, t.episode
ORDER BY time DESC LIMIT 0,24

Is there something going on with my syntax? It looks fine to me but the error is:

SQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE type!="raw")) as time, MAX(t.filler) as filler,m.anime_name, m.furl, m.vid' at line 1

EDIT: Correct answer, but I had to add some grouping as it was returning more than one result. Thank you!

Unrelated to your error, but your WHERE clause is checking the value of m.status to be 0. As this can only be true if there is a row found on anime_list the LEFT OUTER JOIN is not useful. An INNER JOIN will give you exactly the same result, but if you only want to match against status 0 records then keep using the LEFT OUTER JOIN but more the check for m.status into the ON clause of the JOIN (same for the AND t.episode>=m.latest_ep) — Kickstart
– Kickstart, Commented Oct 2, 2013 at 15:30

Sashi Kant · Accepted Answer · 2013-10-02 15:10:14Z

1

You have forgot to add the from table statement

Try this::

SELECT
t.episode,
t.anime_id,
MAX(t.type) as type,
MIN(t.HD) as HD,
MAX(t.thumbnail) as thumbnail,
IF((type="raw"),(MIN(t.saved)), (SELECT MIN(saved) from  anime_episodes WHERE type!="raw")) as time,
// UP HERE ^
MAX(t.filler) as filler,
m.anime_name,
m.furl,
m.video_thumb
FROM anime_episodes t
LEFT JOIN anime_list m ON
( t.anime_id=m.anime_id )
WHERE t.approved=1 AND t.locked=0 AND m.status=0 AND t.episode>=m.latest_ep
GROUP BY t.anime_id, t.episode
ORDER BY time DESC LIMIT 0,24

edited Oct 2, 2013 at 15:10

answered Oct 2, 2013 at 15:02

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hituptony Over a year ago

I believe the table is anime_episodes, considering the beginning of the IF statement he selects type = raw there as well. even though he's not aliasing t.type...

DeathDream Over a year ago

Thank you! However it turns out I had to do some additions for other reasons.

Collectives™ on Stack Overflow

MySQL IF() Sub Select

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related