CODEWITHSUNDEEP
mysql
Olivier Pons
Olivier Pons

Reputation: 15778

mysql: how to return a NULL result when no records found?

Here's the MySQL I'm doing:

mysql> select "Le Café de la Mairie" as titre, "15 pl Mairie" as adresse1, id from ville where sound=soundex('FERTE SOUS JOUARRE') and cp='77260';
+-----------------------+--------------+-----+
| titre                 | adresse1     | id  |
+-----------------------+--------------+-----+
| Le Café de la Mairie  | 15 pl Mairie | 447 |
+-----------------------+--------------+-----+
1 row in set (0.00 sec)

mysql> select "Le Café de la Mairie" as titre, "15 pl Mairie" as adresse1, id from ville where sound=soundex('FERTE SOUS JOUARRE') and cp='77261';
Empty set (0.00 sec)

mysql>

Here's what I want:

mysql> select "Le Café de la Mairie" as titre, "15 pl Mairie" as adresse1, id from ville where sound=soundex('FERTE SOUS JOUARRE') and cp='77260';
+-----------------------+--------------+-----+
| titre                 | adresse1     | id  |
+-----------------------+--------------+-----+
| Le Café de la Mairie  | 15 pl Mairie | 447 |
+-----------------------+--------------+-----+
1 row in set (0.00 sec)

mysql> **(same query but dont find a record)**;
+--------------+------------+------+
| titre        | adresse1   | id   |
+--------------+------------+------+
| NULL         | NULL       | NULL |
+--------------+------------+------+

mysql>

Of course, I've already google & search stackoverflow.

Upvotes: 0

Views: 72

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT
    t.*
FROM 
    (SELECT 1) AS dummy
  LEFT JOIN
    (SELECT ... your select here) AS t
      ON TRUE ;

So, to stick with the question:

SELECT
    t.*
FROM 
    (SELECT 1) AS dummy
LEFT JOIN
    ( SELECT
        "Le Café de la Mairie" as titre,
        "15 pl Mairie" as adresse1,
        id
      FROM ville
      WHERE sound=soundex('FERTE SOUS JOUARRE')
      AND cp='77261') AS t
ON TRUE;

Upvotes: 1

Related Questions