Is there a simple way in Mysql to convert a multi-column resultset into a single-column resultset where each row in the single column contains a single value from each cell in the multi-column resultset?
For instance, say I have a table like:
id | fk1 | fk2 | fk3
1 2 3 4
5 6 7 8
Ideally I'd like to be able to run a query along the lines of:
SELECT <some_function>(fk1, fk2, fk3) AS value FROM myTable;
...and then get an output like:
value
2
3
4
6
7
8
Is there a straightforward way of doing so, or is the only real option to walk the multi-column resultset in code and extract the values into the format that I want?
The end goal is to be able to use the first query as a subquery in a context where the input can only be a single column of values, like:
SELECT * FROM myOtherTable WHERE id IN
(SELECT <some_function>(fk1, fk2, fk3) AS value FROM myTable);
Thus a pure SQL solution is preferable, if one exists.
The simplest way is to use union all:
select fk1 from mytable union all
select fk2 from mytable union all
select fk3 from mytable;
There are other methods, but this is simplest.
For your particular query, you can use exists and or or in:
SELECT mo.*
FROM myOtherTable mo
WHERE EXISTS (SELECT 1
FROM MyTable m
WHERE mo.id IN (m.fk1, m.fk2, m.fk3)
);
EDIT:
For performance, but an index on each fk column and use multiple conditions in the where:
SELECT mo.*
FROM myOtherTable mo
WHERE EXISTS (SELECT 1 FROM MyTable m WHERE mo.id = m.fk1) OR
EXISTS (SELECT 1 FROM MyTable m WHERE mo.id = m.fk2) OR
EXISTS (SELECT 1 FROM MyTable m WHERE mo.id = m.fk3);
This will do index lookups, which should be much faster than the original form.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments