Creating new temporary tables in mysql from selecting two table using PHP

Question

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.

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
– Barmar, Commented Apr 9, 2015 at 7:05
please consider my situation 2. and tell me how to create the table with it — Dhanasekaran
– Dhanasekaran, Commented Apr 9, 2015 at 7: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
– Barmar, Commented Apr 9, 2015 at 7: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
– Barmar, Commented Apr 9, 2015 at 7: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
– Barmar, Commented Apr 9, 2015 at 7:17

Barmar · Accepted Answer · 2015-04-09 07:37:37Z

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

answered Apr 9, 2015 at 7:37

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dhanasekaran Over a year ago

Is it possible to get the result post creating this table?

Barmar Over a year ago

I don't know what you mean by that. Get what result?

Dhanasekaran Over a year ago

select the table and to display

Barmar Over a year ago

SELECT * FROM tablename

Dhanasekaran Over a year ago

I want the fields having NULL values to be merged. How can i achieve this?

|

Collectives™ on Stack Overflow

Creating new temporary tables in mysql from selecting two table using PHP

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related