how to get count of country column and as per count value, get result from other table in MYSQL-CodePudding

I have below tables

I want to combine the result from two tables.

As you can I have country column which contains a string value (max two countries not more than two). If we can split the string, resultant array should contain two elements and we can count the elements as below.

For example,

Rupee => india/nepal => if you split by "/", ["india", "nepal"] => count = 2

dollar => usa/canada => if you split by "/", ["usa","cananda"] => count = 2

yen => japan => (?) , ["japan"] => count = 1

Now this count value is stored in y_table and as per the count value, I want to get other columns in the result set.

So what should be the query to get result as below,

CodePudding user response：

Join the tables with a CASE expression in the ON clause that checks whether the value of the column country contains a '/' or not:

SELECT x.*, y.*
FROM x_table x INNER JOIN y_table y
ON y.count = CASE INSTR(x.country, '/')
  WHEN 0 THEN 1
  ELSE 2
END;

See the demo.