Return row even if nothing found
I have a situation where I need to always get a row returned even if no match is in the table (only 1 or many rows are acceptable). I can use: select a, b, c from mytable where a = 'yarp'; and might get 20 rows if there are matches, but I at least need 1 default row back... using : select ifnull(a,'NOTHING') as a, ifnull(b, 'NOTHING') b, ifnull(c, 'NOTHING') c from mytable where a = 'yarp'; just returns nothing... Anything I can add in here to have a recordset of at least (nothing, nothing, nothing) ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Return row even if nothing found
Cantwell, Bryan wrote: I have a situation where I need to always get a row returned even if no match is in the table (only 1 or many rows are acceptable). I can use: select a, b, c from mytable where a = 'yarp'; and might get 20 rows if there are matches, but I at least need 1 default row back... using : select ifnull(a,'NOTHING') as a, ifnull(b, 'NOTHING') b, ifnull(c, 'NOTHING') c from mytable where a = 'yarp'; just returns nothing... Anything I can add in here to have a recordset of at least (nothing, nothing, nothing) ? Sorry, no. The database can only give you data that it contains. No rows of data = no rows of results. This is a condition you will need to test for in your application and apply the appropriate adjustments to your code. However, if mytable is the child to another table (say myparent), then you can query on ... FROM myparent LEFT JOIN mytable ... and if there were no matches on the matching column then mytable would have all NULL values for its columns. Is that something you can work with? http://dev.mysql.com/doc/refman/5.1/en/join.html -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Return row even if nothing found
On Wed, 16 Dec 2009 07:39:09 Cantwell, Bryan wrote: > I have a situation where I need to always get a row returned even if no > match is in the table (only 1 or many rows are acceptable). > > I can use: > select a, b, c from mytable where a = 'yarp'; > and might get 20 rows if there are matches, but I at least need 1 default > row back... using : > select ifnull(a,'NOTHING') as a, ifnull(b, 'NOTHING') b, ifnull(c, > 'NOTHING') c from mytable where a = 'yarp'; just returns nothing... > > Anything I can add in here to have a recordset of at least (nothing, > nothing, nothing) ? You can do something like: SELECT mytable.* FROM (SELECT 1) AS dummy LEFT JOIN mytable ON id = 'something that does not exists'; It's not pretty, but it might do the trick for you. - Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
