How to combine search results with dbxquery results and include unmatched results from search?

[kjandhyala]

Hi, My search data and database table looks like below.
search data:
id name
111 aaa
222 bbb
333 xyz
444 ccc
555 pqr
666 ddd

database data
srid country
111 united states
222 canada
444 china
666 france

When I write a search query and get additional column from database using dbx query, the unmatched rows are missing. Here is my search query and dbxquery.

index="myindex" | fields srid,name| dbxquery connection="myconn" query="select country from country_lkup;" "id" as "srid" OUTPUT "country"| table srid,name,country

The result is showing as
srid name country
111 aaa united states
222 bbb canada
444 ccc china
666 ddd france

But, I would like to see the results including the rows that do match in my database, as below.
srid name country
111 aaa united states
222 bbb canada
333 xyz
444 ccc china
555 pqr
666 ddd france

Can someone please help me how to achieve this?

[richgalloway · Accepted solution]

I'd try fillnull first .

index="myindex" | fields srid,name| dbxquery connection="myconn" query="select country from country_lkup;" "id" as "srid" OUTPUT "country"| fillnull value="unknown" country | table srid,name,country

If that doesn't work, you may need to try some SQL tricks. My SQL is rusty, but perhaps COALESCE or ISNULL will work.

[manunairadavakk]

@richgalloway
I am getting an error:
Error in 'dbxquery' command: This command must be the first command of a search.