Referencing (Using an alias) the value from nested MySQL query in an outer conditional statement

Question

I'm using a nested query to get the value I need from a table that I then need to use in a conditional statement, however, every time I try this I keep on getting an error saying unknown column (format) in the field list

SELECT

(SELECT format FROM competition_stages WHERE comp_id = "5" AND rid = "24") AS format,

a.tie_id, b.name AS team_a, b.team_id AS team_a_id, c.name AS team_b, c.team_id AS team_b_id, SUM(e.bonus) AS team_a_bonus, SUM(f.bonus) AS team_b_bonus,

SUM(CASE 
    WHEN (a.team_a = e.team_id AND format = "0") THEN e.score
END) as team_a_agg,

SUM(CASE 
    WHEN (a.team_b = f.team_id AND format = "0") THEN f.score
END) as team_b_agg

FROM competition_tie a

INNER JOIN teams b ON (a.team_a = b.team_id)
INNER JOIN teams c ON (a.team_b = c.team_id)

LEFT JOIN fixtures d ON (a.tie_id = d.tie_id)
LEFT JOIN fixture_scores e ON (d.fx_id = e.fx_id AND a.team_a = e.team_id)
LEFT JOIN fixture_scores f ON (d.fx_id = f.fx_id AND a.team_b = f.team_id)

WHERE a.comp_id = "5" AND a.rid = "24" AND a.season_id = "5"

GROUP BY a.tie_id

ORDER BY a.tie_id ASC

I can get the value of the format column in my results when I go through them but it just seems I can't use it within my query to use in.

Thanks for your help!

Would be easier to have SQL create and insert statements included to do testing. — Menelaos, Apr 16 '13 at 19:28

score 0 · Accepted Answer · answered Apr 16 '13 at 19:33

Instead of using a subquery, simply join the competition_stages table to your query so that you can reference the format column directly. Assuming there is no appearant relation between the competition_stages table and the other tables in the query (at least not from the information on hand), you can just join the table using the two conditions you specified if these conditions would only yield 1 result in the competition_stages table. Something like this:

SELECT cs.format, a.tie_id, ....
FROM competition_tie a ...
INNER JOIN competition_stages cs ON cs.comp_id = "5" AND cs.rid = "24"

Perfect! This has done exactly what I needed it to do. Thanks — Mo Gusbi, Apr 16 '13 at 19:50

Referencing (Using an alias) the value from nested MySQL query in an outer conditional statement

1 Answers1