Help Me with my SQL Query (Need it to always return at least one row.)

Question

First off, here's my SQL query:

SELECT research_cost, tech_name,
    (SELECT research_cost
        FROM technologies
        WHERE research_cost <= USERS_RESEARCH_POINTS_VALUE
        ORDER BY research_cost DESC
        LIMIT 1) as research_prev,
    (SELECT cost
        FROM technology_costs
        WHERE id = 18
        LIMIT 1) as technology_cost
FROM `technologies`
    JOIN technology_costs
    ON id = COUNT_OF_TECHS_USER_LEARNED
WHERE research_cost > USERS_RESEARCH_POINTS_VALUE
ORDER BY research_cost ASC
LIMIT 1

Website link: http://www.joemajewski.com/fortress/

Some people might consider the above query to be moderate-sized, or even small; not me. For me, that's one of the largest and most complex SQL queries I've ever written, so I pushed myself to my limits.

Anyways, the query itself has no syntax errors, and it does exactly what I want it to do, for the most part. There's one minor issue, however.

If the main SELECT clause doesn't return a row (the "SELECT research_cost, tech_name FROM technologies WHERE research_cost > whatever"), then the entire query is an empty set.

If it helps, Fortress is this game that I'm working on. It's a browser-based MMORPG where users build an army to try and get a top stop in the leaderboards. Technologies are a game mechanic where users purchase upgrades to their stats (gold income, unit production, etc). Researching is a game mechanic that allows you to learn new technologies by training scientists to do the researching. That's what this query is all about. It grabs the current technology that is being researched, as well as the research cost of it. It additionally grabs the cost in gold of how much the next technology will cost.

So... is there any way that I can force at least a null row to be returned in the result set, as the nested select statements retrieve variables that I need in the script, even if the outer query returns nothing. Or could someone possibly change it around so that a result is always returned.

If anything else is needed, let me know. :)

Answer 1

I would try wrapping it in ISNULL()

Like this:

SELECT research_cost, tech_name, 
ISNULL((SELECT research_cost 
    FROM technologies 
    WHERE research_cost <= USERS_RESEARCH_POINTS_VALUE 
    ORDER BY research_cost DESC 
    LIMIT 1), 0.00) as research_prev, 
    [the rest of your query here]

//Note: instead of '0.00' you'll want some appropriate default value.

Answer 2

You can try getting an empty row with the proper schema first, then try filling those rows in with the query you wrote above. The only downside is the additional query to do so.

That way, if your query above returns nothing, you still have the empty row and you can check on that.

Answer 3

This query always returns at least 1 row

SELECT * FROM (__your old query__)aaa
UNION 
SELECT NULL as research_cost, NULL as tech_name, NULL as research_prev, 
... etc [FROM DUAL] // DUAL is a  dummy table in mysql