Help Me with my SQL Query (Need it to always return at least one row.)
Solution 1
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
Solution 2
An approach that does not require a second select that lists every original column:
SELECT yourstuff.*
FROM (__your original query__) yourstuff
RIGHT OUTER JOIN (SELECT 1 AS fake FROM DUAL) faketable ON 1=1;
If your original query returns any rows, they are passed through unaffected. If it returns no rows, you will get one row of all nulls.
Joe Majewski
HTML, PHP, MySQL, MsSQL, Perl, C++, Java, JavaScript, AJAX, jQuery, IBM Assembler, Git, SVN, Unix, Unity, PhotoShop, GIMP, Blender Oh yeah... I'm #SOreadytohelp on StackOverflow!
Updated on June 05, 2022Comments
-
Joe Majewski almost 2 years
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 FROMtechnologies
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. :)