Return row even if nothing found

I have a situation where I need to always get a row returned even if no mat=
ch is in the table (only 1 or many rows are acceptable).

I can use:
select a, b, c from mytable where a =3D 'yarp';
and might get 20 rows if there are matches, but I at least need 1 default r=
ow back...
using :
select ifnull(a,'NOTHING') as a, ifnull(b, 'NOTHING') b, ifnull(c, 'NOTHING=
') c from mytable where a =3D 'yarp';
just returns nothing...=20

Anything I can add in here to have a recordset of at least (nothing, nothin=
g, nothing) ?=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.org