0

I have two tables with different fields in that and having only one common field as primary key/foreign key. How can i create temporary table with new fields in that, while selecting some of the fields from two existing tables? And i need to insert values once after the temp table creation.

Situation 1: I need to do this when i need to generate some reports from two existing tables in one single click like create table if not exists already, insert values in it, have to produce the result. have to do this all in single click.

situation 2: I have rank field in table 2. According to rank field, new fields can be created in the new temp table. For ex: if rank>1<100, then rank1 field has to be created in temp table. if rank >101<200, then rank2 field has to be created.

Situation 3: If the select query generates number of records then i want to insert that in one row itself without making too many rows

Ex:

If i am producing some results as the following way

Date rank
11-01-2015 11
11-01-2015 120
11-01-2015 210

i need to create temp table in such a manner to combine all the ranks with only one date field in it and with rank1=11, rank2=120 and rank3=210

Then i can be able to get all ranks through the date in single query.

Dhanasekaran
  • 107
  • 1
  • 2
  • 13
  • 2
    Write a query that returns the data you need. Then use `CREATE TEMPORARY TABLE tablename AS SELECT ...` where you put that query at the end. – Barmar Apr 09 '15 at 07:05
  • please consider my situation 2. and tell me how to create the table with it – Dhanasekaran Apr 09 '15 at 07:12
  • You'll need to write dynamic SQL to do that. But why would you want to have different column names in the temp table, how will you know what column to use when you query it? You're probably going about it wrong if you need to do that. – Barmar Apr 09 '15 at 07:14
  • I'm not even sure I understand situation 2. You can't have different field names for different rows of the table. Maybe you're not explaining it clearly enough for me. Show some sample input and what you want the temp table to look like. – Barmar Apr 09 '15 at 07:17
  • I don't understand #3, either. Please show examples. If you want to combine data from multiple rows, maybe you're looking for `GROUP_CONCAT`. – Barmar Apr 09 '15 at 07:17
  • If i create a temp table with fields like rank1, rank2, rank3,...rankn. then i will check the rank range and will use it. for ex, if rank>1<100 i will use rank1, elseif rank>101<200, i will use rank2. – Dhanasekaran Apr 09 '15 at 07:18
  • I still don't understand. Please update the question and show an example of what you're trying to do. I can't figure out if you're talking about how to fill in the `rankN` fields, or how to join with them when you're using the temp table. – Barmar Apr 09 '15 at 07:21
  • it is not n number of fields, but 4 number of ranks. but when inserting values i need to check the condition that where should i insert. For ex: if rank between 1 to 100, then rank1 should be added. if rank between 101 to 200, then rank2 should be added. rank is between 1 to 400 in total. – Dhanasekaran Apr 09 '15 at 07:33
  • This is called pivoting: http://stackoverflow.com/questions/7674786/mysql-pivot-table – Barmar Apr 09 '15 at 07:34

1 Answers1

1

Situation 2:

CREATE TEMPORARY TABLE tablename AS
SELECT date, 
       MAX(CASE WHEN rank BETWEEN 1 AND 100 THEN rank END) AS rank1,
       MAX(CASE WHEN rank BETWEEN 101 AND 200 THEN rank END) AS rank2,
       MAX(CASE WHEN rank BETWEEN 201 AND 300 THEN rank END) AS rank3,
       MAX(CASE WHEN rank > 300 THEN rank END) AS rank4
FROM table2
GROUP BY date
Barmar
  • 741,623
  • 53
  • 500
  • 612