Question 20143 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql Query Result with N/A

Currenty I have records like below :

  • name|score|

    tony|23|

    john|32|

    eddy|25|

    mark|21|

    nick|26|

    alex|27|

    nick|23|

I want to request table with only name like : tony, john, eddy, mike, paul
using this query below ;

SELECT name,
name
FROM a_team
WHERE (name LIKE  'tony%' or name LIKE 'john%' or name LIKE 'eddy%' or name LIKE 'mike%' or name LIKE 'paul%')

the result is :

  • name|score|

    tony|23|

    john|32|

    eddy|25|

Since name like: mike and paul have no record yet
how do I request table with above query with result displaying
mike and paul = "n/a" in field "score" as below ?

  • name|score|

    tony|23|

    john|32|

    eddy|25|

    mike|n/a|

    paul|n/a|

Thanks for response

Tags:

2

Answers


  1. 0

    You cannot transfer the names from the criteria to the output rowset. You must make your criteria a rowset and join existing data to it:

    SELECT criteria.name, COALESCE(a_team.score, 'N/A') score
FROM (
    SELECT 'tony' AS name UNION ALL
    SELECT 'john' UNION ALL
    SELECT 'eddy' UNION ALL
    SELECT 'mike' UNION ALL
    SELECT 'paul'
    ) criteria
LEFT JOIN a_team ON a_team.name LIKE CONCAT(criteria.name, '%')
    Login or Signup to reply.
  2. 0

    1- You can set the default values for the column, so when there is no value in the field it will return the default value from the database, for example for scores if your table name is scores you can run the query;

    ALTER TABLE a_team MODIFY COLUMN scores VARCHAR(20) DEFAULT "N/A";

    NOTE: N/A cannot be set for an integer field so you have to use varchar()

    2- You can use IFNULL to replace the null values from the column:

    SELECT name, IFNULL(score, 'N/A') AS score
FROM a_team
WHERE (name LIKE  'tony%' or name LIKE 'john%' or name LIKE 'eddy%' or name LIKE 'mike%' or name LIKE 'paul%')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search