Question 112970 in Mysql Question posted in
The official MySQL documentation can be found here.

a query to check value in other table – Mysql

I have below tables

enter image description here

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.

Tags:

2

Answers


  1. 0

    Mapping a value to a column name is not inherent part of the relational algebra of SQL. Meaning: better not done.

    Instead make an other table instead y_table

    y_table’

    A column value probably not needed.

    continent type value
    Asia Rupee_Single 1
    … … …

    Then the SQL is no problem.

    Login or Signup to reply.
  2. 0 
    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search