Re: Enforce value on select

[Santino]

SELECT  nameColumn,
otherColumn, count(*) as flag  FROM theDatabase WHERE rowID = 1 group by rowID;
or make a temp table with the IDs and then left join it with 
theDatabase and drop temp.
Santino

At 23:27 -0700 10-10-2004, John Mistler wrote:
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]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]