Subqueries Override On Not NULL

[MrFish]

I've got a situation where I need a subquery to set some value in a table to a value of the first row in another table. At times this other value may be NULL so there is a possible backup value I can pull in that situation. Without doing this in the code can I do this in the query? Here is my query-

SELECT  *,  (SELECT modelphoto_file FROM modelphoto WHERE modelphoto_model_id=model_parent AND modelphoto_file != '' LIMIT 0, 1) as image,  (SELECT modelphoto_file FROM modelphoto WHERE modelphoto_model_id=model_id AND modelphoto_file != '' LIMIT 0, 1) as image,  FORMAT(model_price,0) as model_price,  FORMAT(model_sqft,0) as model_sqft  FROM communitymodel	JOIN community ON communitymodel_community_id=community_id	JOIN model ON communitymodel_model_id=model_id	WHERE model_show='1' AND community_id='{community_id}'	ORDER BY model_name ASC

The important lines are the first 2 after SELECT *,. The second will overwrite the first even if the second is an empty string. So can I wrap it in some kind of condition that says only set the value of image if a result was found. I don't even need an if-else since if the second line overwrites the first with a legitimate value it's preferred.

[justsomeguy]

Why wouldn't you use an if statement for this? It sounds like you want to use a certain value if something is true, or else use a different value.

[MrFish]

I could in the code but I was trying to get away from doing that. The app that I'm working on shows the details for one of these models. The information that it reads the parameters from is in an XML file that it parses to JSON. It expects an image parameter and a few other key-value parameters. I want to keep the app modular to where it doesn't need to look for an image1, image2, image3, image4, etc. parameter. In a perfect world it would just read the image parameter without knowing where it came from. So if possible I want to put the conditional in the sql that loads the xml. The c-load property below is a custom tag that a script looks for and loads it's contents using the rows from the query. This takes an array of rows loaded from the community table and runs a query to find all models it's associated with in the communitymodel table. The communitymodel table does a join on the model table and the first row in modelphoto that can matches the model_id field is used as the image. The previous programmer had some setup where the models could parent other models and to get the image you would instead use model_parent to find the image in modelphoto instead of model_id.

<c-load connection="main" table="community" order="community_order">  <panel name="com_modelresults_{community_name}" class="ModelResultsPanel">   <data title="Community Floorplans" start="0" limit="8">   <c-load connection="main" query="SELECT *, (SELECT modelphoto_file FROM modelphoto WHERE modelphoto_model_id=model_parent AND modelphoto_file != '' LIMIT 0, 1) as image, (SELECT modelphoto_file FROM modelphoto WHERE modelphoto_model_id=model_id AND modelphoto_file != '' LIMIT 0, 1) as image, FORMAT(model_price,0) as model_price, FORMAT(model_sqft,0) as model_sqft FROM communitymodel JOIN community ON communitymodel_community_id=community_id JOIN model ON communitymodel_model_id=model_id WHERE model_show='1' AND community_id='{community_id}' ORDER BY model_name ASC">	<option key="{model_name}" value="com_modeldetail_{community_name}_{model_name}" image="http://somesite.com/CmImageDir/{image}">	 <line><attribute value="{model_maximum_beds} Bedrooms"/><attribute value="{model_bathfull_minimum} Baths"/></line>	 <line><attribute value="${model_price}"/><attribute value="{model_sqft} Sq. Ft."/></line>	</option>   </c-load>   </data>  </panel></c-load>

So to keep this app from needing to know all this crap it would be easily reusable if it wasn't aware of where the content was coming from (abstraction of course).

Edited March 6, 2013 by MrFish

[justsomeguy]

I was assuming you would put the if statement in the SQL query. http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

[MrFish]

Ah ok. But I need to make a custom function for this don't I? I'll have a look.

[justsomeguy]

The functions listed on that page are for use in queries, there is another set of control flow statements for stored procedures.