a query to check value in other table - Mysql

Question

I have below tables

DEMO fiddle

In x_table, I have different records. I want to fetch all currencies from x_table where continent is Asia which is straight forward as below,

1. SELECT currency from x_table where continent='Asia'

and it should return Rupee and Yen rows which is also fine.

Now look at type columns in x_table and then another y_table table. type value represents different columns in y_table

Now query should be (considering two tables)

Fetch all currencies from x_table where continent is something BUT check relative type column in y_table. If respective type column value is 1 then and then fetch the record otherwise ignore it.

something like

SELECT continent, currency FROM x_table as X inner join y_table as Y on X.continent = Y.continent (BUT check if matching "type" column value is 1) if it is 0 ignore it.

With this logic, if you consider 1. query again, it should return only Rupee row because Rupee_Dual in y_table for Asia cotinent is 1. But Yen row should not return because Yen_Single in y_table for Asia continent is 0.

Answer 1

SELECT x_table.*,
       CASE LOCATE('/', x_table.country) 
           WHEN 0
           THEN 'Single'
           ELSE 'Dual'
           END AS country_count,
       CONCAT(x_table.name,
              '_',
              (SELECT country_count)
              ) AS type,
       CASE (SELECT type)
            WHEN 'Rupee_Single' THEN y_table.Rupee_Single
            WHEN 'Rupee_Dual' THEN y_table.Rupee_Dual
            WHEN 'Dollar_Single' THEN y_table.Dollar_Single
            WHEN 'Dollar_Dual' THEN y_table.Dollar_Dual
            WHEN 'Yen_Single' THEN y_table.Yen_Single
            WHEN 'Yen_Dual' THEN y_table.Yen_Dual
            END AS enabled                  
FROM x_table
JOIN y_table USING (continent)
-- WHERE continent = 'Asia'
-- HAVING enabled

https://dbfiddle.uk/ce9Q4NEX