Question: I need to get users from my database, by this query :

```
SELECT * FROM users
WHERE first_name = 'Aragorn'
AND last_name = 'SonOfAratorn'
AND city = 'Osgiliot'
AND country = 'Gondor'

```

The problem is that in my app, i will often have city or country missing, or both of them missing.

I can make 3 or four HQL queries for that, but that seems like a not very efficient solution. 

Is there a way to have just one query which will accommodate the cases when one or both of these attributes (city and country) is null, and how do i convert that query to HQL ?

My DB is on MySql Server.

PS :

I have tried something like this, but it didn't work :

```
SELECT * FROM users
WHERE first_name = 'Aragorn'
AND last_name = 'SonOfAratorn'
AND city like IFNULL('%', <my-city-param>)
AND country like IFNULL('%', <my-country-param>);

```

I'm looking for something like that in HQL format.

Thanks in advance. :)

Answer: 
Check the **coalesce** function; 

```
coalesce(:valueifnotnull,'valueifnull') 

```

should do the trick
```
SELECT * FROM users
WHERE first_name = 'Aragorn'
AND last_name = 'SonOfAratorn'
AND (city is null or @city is null or city like '%' + @city)
AND (country is null or @country is null or country like '%' + @country)

```