I have below tables
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,
- 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
.
CodePudding user response:
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.
CodePudding user response:
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