CODEWITHSUNDEEP
mysqlsqlsplit
EmilioMg
EmilioMg

Reputation: 376

Insert multi column result into single column table

I have a table 'movies' with three Columns: 'id', 'master_id' and 'searchMe' (simplified). I have another Table 'temp_ids' with a single column: 'id'. It is a temporary table, but I don't think that matters.

When I make a query on my table 'movies' like

SELECT `id`, `master_id` FROM 'movies' WHERE searchMe = '1';

I get a multi column result. Now I want to insert every id and every master_id into the 'temp_ids'-Table, but one at a time. So if my result is

id_1 | master_1
id_2 | master_2
id_3 | NULL

I want my temp_ids to look like

id_1
master_1
id_2
master_2
id_3

So I want to convert every single column in the result into its own row. How can I do that in an elegant way? I know I can do it in multiple queries, searching for id and master_id separatly, and I know I can solve that problem with PHP or so. But I would prefer it to solve that problem in a single mysql-query, if such a thing is possible.

I made a sqlfiddle for this: http://sqlfiddle.com/#!2/b4a7f/2

Upvotes: 3

Views: 310

Answers (2)

Taryn
Taryn

Reputation: 247760

To SELECT the data you can use a UNION ALL for this:

SELECT  `id`
FROM movies
WHERE searchMe = 1
union all
SELECT `master_id`
FROM movies
WHERE searchMe = 1
  and master_id is not null

see SQL Fiddle with Demo

Doing it this way, you cannot distinguish between what value comes from each column, so you can always add an indicator, this will give you two columns but then you know where the data came from:

SELECT  `id`, 'id' type
FROM movies
WHERE searchMe = 1
union all
SELECT `master_id`, 'master'
FROM movies
WHERE searchMe = 1
  and master_id is not null

Then you would just use this query to INSERT INTO temp using this SELECT

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

It would be like this

INSERT INTO temp_ids(id)
SELECT id 
FROM
(
   SELECT id
   FROM FirstTable 
   UNION
   SELECT master AS id
   FROM SecondTable 
) t

Upvotes: 2

Related Questions