Good idea. liang le's answer almost got it, but I couldn't make it work with string values quite right. Here is my situation:
I am issuing a series of queries all-in-one like "SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 1;SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 2;SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 3;" expecting a return of aName otherItem aName otherItem aName otherItem but if one of those rowIDs does not exist, then I get aName otherItem aName otherItem and my app has no value for the non-existing row. I would like for the query to return an indication that the row did not exist, like: aName otherItem 0 or '' or 'NULL' (no nameColumn entries will ever = 0, or '', or NULL) aName otherItem Does that make more sense, and if so, is there a solution? Thanks, John Liang Le -- Your query: (SELECT IFNULL(a.nameColumn,'0') nameColumn, IFNULL(a.otherColumn,'0') otherColumn FROM theTable a WHERE a.rowID = 5) UNION (SELECT IFNULL(b.nameColumn,'0') nameColumn, IFNULL(b.otherColumn,'0') otherColumn FROM theTable b WHERE b.rowID = 5) ; Worked when the row DID NOT exist (like I asked for). However, when the row DID exist, it was returning: aName otherItem 0 0 The zeros are troublesome. It should look like: aName otherItem Thanks! on 10/10/04 10:46 PM, Michael Stassen at [EMAIL PROTECTED] wrote: > Then how will you know the difference between a row with nameColumn = 0 (or > '') and one that doesn't exist? > > What you are asking for seems very strange. You want the db to pretend > there's a value for nonexistent rows. If rowID 5 should have the value 0, > then I wonder why there isn't a row with rowID=5 and value=0. If it's just > a matter of treating non-existent rows as having 0 value in your app, why > don't you simply code that into your app? In other words, I find it hard to > provide a solution, because I don't understand what you want. Perhaps if > you explained it, someone could suggest how best to accomplish it. > > Michael > > John Mistler wrote: > >> Thanks for the reply. There is a slight difference in what I need from the >> "IFNULL" function. It will only return the specified value if the column is >> null on a row that actually exists. I am needing a function that will >> return the specified value if the row does NOT exist. Any other ideas? >> >> SELECT nameColumn from theDatabase WHERE rowID = 5; >> >> (when no row has ID "5") >> >> result --> empty set (I want a value like '0' or something) >> >> Thanks again! >> >> -John >> >> on 10/10/04 8:12 PM, liang lei at [EMAIL PROTECTED] wrote: >> >> >>> --- John Mistler <[EMAIL PROTECTED]> >>> >>>> Is there a way to force SOME value to be returned >>>> from a SELECT query when >>>> the result is empty set? For instance: >>>> >>>> SELECT nameColumn from theDatabase WHERE rowID = 5; >>>> >>>> (when no row has ID "5") >>>> >>>> result --> empty set >>>> >>>> I would like for it to return some value, such as '' >>>> or 0 . . . >>>> >>>> Thanks, >>>> >>>> John >>>> >>>> >>>> -- >>>> MySQL General Mailing List >>>> For list archives: http://lists.mysql.com/mysql >>>> To unsubscribe: >>>> >>> >>> http://lists.mysql.com/[EMAIL PROTECTED] >>> >>>> >>> select ifnull(column,'0') from table > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
