MYSQL temporary table

michael789
michael789 used Ask the Experts™
on
I need to create a temporary table using mysql in order to change my data layout. My table includes data per month if a salary was paid or not for a specific month (INT 1 or 0). The field lohn1, lohn2, lohn3 etc. just has the value 0 or 1 based on if a salary was paid or not and the field lohn1m, lohn2m, lohn3m etc. just holds the month number it corresponds to. e.g. 1,2,3 etc.

CREATE TABLE `listmain` (
  `id_list` bigint(11) NOT NULL auto_increment,
  `lohn1` int(1) default '0',
  `lohn1m` int(11) default '1',
  `lohn2` int(1) default '0',
  `lohn2m` int(1) NOT NULL default '2',
  `lohn3` int(1) default '0',
  `lohn3m` int(1) NOT NULL default '3',
  `lohn4` int(1) default '0',
  `lohn4m` int(1) NOT NULL default '4',
  `lohn5` int(1) default '0',
  `lohn5m` int(1) NOT NULL default '5',
  `lohn6` int(1) default '0',
  `lohn6m` int(1) NOT NULL default '6',
  `lohn7` int(1) default '0',
  `lohn7m` int(1) NOT NULL default '7',
  `lohn8` int(1) default '0',
  `lohn8m` int(1) NOT NULL default '8',
  `lohn9` int(1) default '0',
  `lohn9m` int(1) NOT NULL default '9',
  `lohn10` int(1) default '0',
  `lohn10m` int(1) NOT NULL default '10',
  `lohn11` int(1) default '0',
  `lohn11m` int(1) NOT NULL default '11',
  `lohn12` int(1) default '0',
  `lohn12m` int(1) NOT NULL default '12',
  PRIMARY KEY  (`id_list`),

Now I need to take this values and run a query to create a temporary table that has the following structure:

id_temp_table (autoincrement)
month_number (which is the specific number of the month stored in lohn1m etc.)
value_salary (which hold for the specific month the  = 1)

So pretty much all values where a salary is selected/checked need to go into this table

Thank you for your help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
you will need a UNION ALL ...each part for a columnXX ...
select 1 , idlist, lohn1,  lohn1m from yourtable
union all
select 2 , idlist, lohn2,  lohn2m from yourtable
union all
select 3 , idlist, lohn3,  lohn3m from yourtable

,.... etc 

Open in new window

Author

Commented:
i don't understand. if i do that how would this go into a temp table...

I would run my query like this:

Select*
From listmain
WHERE
(lohn1 = 1 AND lohn1m = 1) OR
(lohn2 = 1 AND lohn2m = 1) OR
etc....

This way I have all records I need but now I need to create a temporary table using the data above. I just don't know how to create this query to get the data above into the format:

CREATE TEMPORARY TABLE tablename
etc....

id_temp_table (autoincrement)
month_number (which is the specific number of the month stored in lohn1m etc.)
value_salary (which hold for the specific month the  = 1)

thank you very much
two questions:

1. Are you going to store the id_list value anywhere in your final table? Right now it appears that you don't need it.
2. Are you going to store the months where the salary is 0, or only those months where the salary is 1?

In any event, the CREATE TABLE syntax is the same for a temporary table as a permanent table.

CREATE  TEMPORARY TABLE `temp_table` (
  `id_temp_table` INT NOT NULL ,
  `month_number` TINYINT NOT NULL ,
  `value_salary` TINYINT NOT NULL ,
  PRIMARY KEY (`id_temp_table`) );


Then, to fill the table, you would do something like this

INSERT temp_table (month_number,value_salary)
SELECT 1, lohn1 from listmain
UNION SELECT 2, lohn2 from listmain
UNION SELECT 3, lohn3 from listmain
....
UNION SELECT 12, lohn12 from listmain


Just so you know, this query loses a lot of data, are you sure this is what you want?

Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Author

Commented:
thank you...
No, I don't need to store this into a final table and I don't need to get the data where the salary is 0.

Can you explain me really quick why I am loosing a lot of data with the query?
sorry, thanks
if you don't need the 0's, then modify the insert to this:

INSERT temp_table (month_number,value_salary)
SELECT 1, lohn1 from listmain WHERE lohn1=1
UNION SELECT 2, lohn2 from listmain WHERE lohn2=1
UNION SELECT 3, lohn3 from listmain  WHERE lohn3=1
....
UNION SELECT 12, lohn12 from listmain WHERE lohn12=1

When I said you were losing data, I meant that you were losing the ability to tie the listmain.id_list variable back to the salaries. In other words, from your temporary table, you could never know who got paid in which month, only that someone got paid in that month.

Also, your new table is inefficient because the value_salary is always going to be 1. You really don't need that column.

Author

Commented:
thank you. That is fine because I just need to create a chart to COUNT() the values for each month. I don't need the query for other items. It is too difficult to create a query in the current format of the data to build a dynamic bar chart. That's why the idea to change the data and store it in a temporary table to use it to build the chart.
Maybe you want this:

CREATE  TEMPORARY TABLE `temp_table` (
  `month_number` TINYINT NOT NULL ,
  `value_salary` TINYINT NOT NULL ,
  PRIMARY KEY (`month_number`) );

INSERT temp_table (month_number,value_salary)
SELECT 1, count(*) from listmain WHERE lohn1=1
UNION SELECT 2, count(*) from listmain WHERE lohn2=1
....
UNION SELECT 12, count(*) from listmain WHERE lohn12=1

Author

Commented:
great....thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial