MySQL Query help

[Stuart-Hannah]

I am trying to load data from one table to another.

 

In the source table (tbl_MISData) it has the data from the MIS, so for example it will have a column called Period - MONA:1, MONA2 and so on and one called Staff. ABC, DEF, and so on. What I am trying to do is get the data from this table another table. Here the problem, my table has all the columns in the table so for instance Monday is MON1, MON2, MON3, MON4, MON5, Week, Start_date and the table from MIS only has the lessons and not the frees so I was trying to MySQL this from one table to another and whatever it doesn't transfer will leave the default values of Free in.

 

MySQL copy code (not working)

SELECT * FROM `tbl_misdata` WHERE `MIS_Room` = 'IT1' OR MIS_Period = 'MONA%';

UPDATE it_booking_i1

SET Mon1 = 'MonA:1'

WHERE it_booking_i1.Week = "A";

 

I am trying to get it to pull any information that matches the Mon where the Week matches the data.

 

I hope I have explained this the best I can.

 

Stuart

[pcstru]

I'm a little curious as to why are you trying to copy the data if the data already exists. If you need to make a report - you might consider creating a view. A view looks like a table but always sources from the maintained data. Anyhow ... the solution possibly has the same root; cross tab queries / pivot tables.

 

If we have tables as described but we want to present data that is held vertically (in columns) horizontally as fields, then we can use grouping and aggregate functions to do this.

 

Table1 
[b]Period   Staff[/b]
MonA1     STA
MonA3     STA
MonA1     STB
MonA2     STB
MonB1     STA

View
[b]Staff   MonA1   MonA2   MonA3   MonB1[/b]
STA        Y                  Y          Y
STB        Y          Y

 

(sorry formatting doesn't hold up well but hopefully you can see the intent!)

 

To do this the SQL might look something like :

 

Select unique( staff )

MAX(case when Period = 'MonA1' then "Y" else "" end) MonA1

MAX(case when Period = 'MonA2' then "Y" else "" end) MonA2

MAX(case when Period = 'MonA3' then "Y" else "" end) MonA3

MAX(case when Period = 'MonB1' then "Y" else "" end) MonB1

MAX(case when Period = 'MonB2' then "Y" else "" end) MonB2

group by staff

 

Which will produce one row per member of staff (assuming unique staff codes) with the period data indicated in the appropriate column.

 

(I say something like because I don't have my examples to hand or time to create the tables to test syntax etc, but hopefully this is enough to push you pretty far along in the right direction).

Edited November 8, 2016 by pcstru

[Stuart-Hannah]

Thanks for the reply.

 

Basically I am running a report once or twice a year from SIMS which gives me a CSV. From the CSV I will get the information about the room, with data looking like the following that's imported into MySQL.

 

Room Name Period Class name Staff Code

Room1 MonA:1 7L/IT1 ASm

Room1 MonA:2 8R/Ma1 PKa

 

 

CREATE TABLE `tbl_misdata` (

`MIS_ID` int(11) NOT NULL,

`MIS_Room` varchar(200) NOT NULL,

`MIS_Period` varchar(200) NOT NULL,

`MIS_Class` varchar(200) NOT NULL,

`MIS_Staff` varchar(200) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

My Table (it_booking_Room1) has the structure of 'start_week' | MON1 | MON2 .. | Week

 

So what I am trying to do is import the data from the mis table to the room table. Each week within the room table has the Week column so I am trying to use that in the query to enable me to work out if it's an A or B week and then copyt that data over.

 

Hope that makes sence.

 

Stuart

[pcstru]

To use a view, you can just

create view as select * from blah

, but the actual query can be as complex as you like. Next time you need the report, just

select * from view

- job done. It will be up to date because it queries the data. No need to create and drop intermediate tables - hopefully.

 

The complex select is just a pivot - honest guv, but it might be quite a complex query. I don't quite get :

 

My Table (it_booking_Room1) has the structure of 'start_week' | MON1 | MON2 .. | Week

 

If we wanted to enumerate the 'week' data from the Period field, we might :

 

select distinct MIS_Period from tbl_misdata;

 

We could enumerate just part of that Period attribute space,

 

select distinct ( LEFT(MIS_Period,3) ) possible_days from tbl_misdata;

 

(Mon-Fri)?

 

Again, I can't run these queries so they might be broken syntactically - sorry. The point is you can generate result sets (tables (which can be views)) of possible values.

 

Create view possible_sessions as select distinct( right(MIS_Period,1) ) from tbl_misdata;

 

Might work if my assumptions about your data hold. It should be the number of slots you have in the school day?

 

That kind of enumeration might help create the final dataset (table) you want because to pivot on compound attribute (where information is coded into the columns attribute) you occasionally need to sort of generate more rows as an intermediate stage even if the pivot relies on grouping to consolidate some arbitrary criteria to a single row.

 

Anyway if you can elaborate some on the output :

 

My Table (it_booking_Room1) has the structure of 'start_week' | MON1 | MON2 .. | Week

 

You are also either creating a table for each booking room or you are going to a lot of trouble for one room?

[Stuart-Hannah]

Thank you soo much for such a details response. It's definitely giving me a starting point for me to build the system!

 

I have just been banging my head at this part for a while now.

 

Hopefully I'll have this understood before I k is it

[pcstru]

It's the kind of thing that is relatively easy to do in a procedural language but quite difficult to grasp as a relational query. Anyhow a little more on the pivot :

 

Say we have a table which describes holiday spending having two columns, HolCode and Spend. Our HolCode might be seasons "Aut", "Spr" etc and each time we go on holiday and spend some money we just record it :

 

Aut, 2.50

Aut, 1.10

Aut, 34.70

Spr, 10.72

Spr, 3.74

Spr, 8.93

 

Every year we want a report on the minimum we spent, the maximum, the average and the total for each holiday. We can just create a query for that and pivot the data around the Code :

 

Select HolCode,
      Max(Spend) MaxSpend,
      Min(Spend) MinSpend,
      Avg(spend) AverageSpend,
      sum(spend) TotalSpend
 from holiday_spending
group by HolCode

 

Which should be about the simplest kind of pivot. But we can also throw some arbitrary processing in there - the kind of thing we like to do procedurally in a loop. So we might like to know how many times we spent over £10. The easy way would be a query just for that but we want it in our report as a column. MySQL allows you to use an IF function within a query (MsSQL would use case, Oracle ISTR was IIF) and we can embed that within a sum.

 

Select HolCode,
      Max(Spend) MaxSpend,
      Min(Spend) MinSpend,
      Avg(spend) AverageSpend,
      sum(spend) TotalSpend,
      sum(if(spend > 10.0, 1, 0) ) NoOverTen
 from holiday_spending
group by HolCode

 

(again, I'm doing this from memory - I can't test at the moment so apologies if there are errors but they should be just syntax, the concept is sound ). So you just need to figure what you need to pivot around (what you are grouping by) and what you are looking at to generate the result you want from the source data for each of your MON1, MON2 etc columns.

 

Hopefully that will see you through but if not shout, I'm happy to help.