MySQL: select one result per row against a list of values matching a field, even with no match, and the list includes duplicates

user_5811670 0
Viewed 28

I want to get a result set in mysql where there is one result for each value in a list (with some other conditions), with a bonus if this list can include duplicates. I have run into similar problems before and often have to get creative with solutions, requiring some external manipulation of datasets via code or cli utils.

Example dataset:

tenant_id item_id other_col
One Two Five
One Three Six
One Four Seven
Eight Nine Ten

What I want to do is roughly:

SELECT coalesce(item_id, 'none')
from table_name
where tenant_id = 'One'
  and other_col in ('Five', 'Six', 'Five', 'ValueNotInCol');

And result set should be:

coalesce(item_id, 'none')
Two
Three
Two
none

In reality, the result set for this query is:

coalesce(item_id, 'none')
Two
Three
mysql sql
1 Answers

The way to solve this is:

  1. Create a temporary table from your data.

  2. Do a left join from your tmp table to the table you want to search.

I sought a solution that didn't require writing to the db, but this is sufficient.

user_58116700
Related