1

I have the following data :

SELECT
   city,
   region,
   postcode
FROM
   AREA
WHERE
   city IN ( 'Jakarta', 'Jabodetabek' )

with the result :

enter image description here

I want the result like this

enter image description here

I've tried several tutorials, including here

https://stackoverflow.com/questions/17832906/how-to-check-if-field-is-null-or-empty-in-mysql

but the result is the same as the first image

1
  • Why should 'Jakarta Timur' match either of those?
    – Rick James
    Feb 10, 2021 at 21:11

1 Answer 1

1

You must convert your values list to rowset.

For example:

SELECT
   city,
   area.region,
   area.postcode
FROM ( SELECT 'Jakarta' city UNION ALL
       SELECT 'Jabodetabek' ) cities 
LEFT JOIN area USING (city)

This query will give NULL for the row for which there is no city value in area table. If you need some custom string literal then use COALESCE() function, i.e. not SELECT city, ... but SELECT COALESCE(city, 'Empty') city, ....


Depends on MySQL version you may use another methods: ROW() function, JSON_TABLE() function, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.