Update MySQL table from 2 or 3 other tables

[Mahmoud]

Dears,

I really need your help. I have a project for automatically assign people to exams locations and timings. I have 3 tables, 1 containing places, 1 timings, 1 people and their roles. I want a query that assign 1 person from role 1 and 1 person from role 2 with a timing and a place and to do this randomly and equally . So, it will repeat the person only if all people are assigned before. I really appreciate your help.

This is as far as I could research ... this example

UPDATE member m1

JOIN ( select id, floor(1+rand()*10) as rnd from member ) m2 on m1.id=m2.id

JOIN names n on n.id = m2.rnd

SET m1.name=n.name

 

But this doesn't do all, please accept my great apology.

 

Mahmoud

[requinix]

Your description would probably make a lot more sense if we could see the table structures and some sample data.

[Barand]

How many people, places times and roles?

What are the constraints re number of people in one place at the same time?

And yes, as @requinix

[Mahmoud]

Dears,

 

Really thanks for your replies.

I have about 100 people with 2 roles (1: phd, 2:employee)

I also have about 68 places and about 120 timings

I should select 1 phd and 1 employee in each timing

I should repeat if all people assigned before. Also, I can repeat in the same day but in another timing.

timing is like following:

Roles:

Places:

Thanks and really appreciated.

 

[Mahmoud]

For Staff:

Thanks

[Barand]

Is the objective

• to fill every location/time combination, or
• just to allocate everyone to at least one location/time?

[Mahmoud]

2 hours ago, Barand said:

Is the objective

• to fill every location/time combination, or
• just to allocate everyone to at least one location/time?

Dear Barand,

 

My objective is to allocate 1 person with role 1 and 1 person with role 2 in a timing randomly without repeating.

To fill all timings with this combination.

 

Thanks

[Barand]

You have 120 times and 100 people

Assuming a ratio of php to employee of 1:4 then 80 employess will go into one timeslot each and 40 of those will go into a second remaining timeslot. Phps then then be shuffled and allocated in rotation to the timeslots (with a check that php/employee combinations are not repeated)

Only one location would be required.

Correct?

[Mahmoud]

Very correct Barand

[Barand]

Then what is the relevance of the 68 locations?

[Mahmoud]

Sorry if I forgot to mention the locations .

Every location has 2 timings per day.

So please assume that locations are already assigned with timings.

 

[Barand]

So you have a table (previously unmentioned) of these locations/time assigments? ie an exam timetable.

Perhaps you could post a data dump of this table?

[Mahmoud]

Sure.

It looks like this.

and every location has 2 assigned timings per day.

 

[Barand]

If you can load that into a database to create a replica of your timetable data, then you are a better man than I.

It tells me nothing about the problem at hand. For example, is this a possible situation ?

location |  time
---------+---------
    1    |    1
    1    |    2
    2    |    1      
    2    |    2      
    3    |    1      
    3    |    2      
    4    |    1      
    4    |    2 

where, as you said, each location has two times. However, each time has four locations (simultaneuous exams) which greatly affects the complexity of the algorithm if we have to avoid scheduling people to be at more than one location at the same time. That is why I would like to see the actual data.

If providing the full information is too much trouble, I am quite happy to walk away.

How are you doing with the problem at your end?

[Mahmoud]

Sure I can provide you will all the data, it is no secret.

The last table to be filled is like this:

As you see, the date and time re in 1 column, the location is already added as each location has to timings 1 starts 9:30 AM and 1 starts 1:00 PM.

As you figured out this is exam location schedule.

[Barand]

If that is all the data then, no problem. Job done

   ID    |  timing                   |  location        |   PhD   |    Emp  |
---------+---------------------------+------------------+---------+---------+ 
    1    |  20/11/2018 09:30         | North Corridor   |    1    |     3   |
    2    |  20/11/2018 13:00         | South Corridor   |    2    |     4   |
---------+---------------------------+------------------+---------+---------+ 
    

Have a good life.

[Mahmoud]

No No , I'm preparing the full data for you.

This is only an example for the completed table.

[Barand]

3 hours ago, Mahmoud said:

No No , I'm preparing the full data for you.

Zzzzzzzzzzzz! Are you manually typing all the the data ?

[Mahmoud]

34 minutes ago, Barand said:

Zzzzzzzzzzzz! Are you manually typing all the the data ?

Not really.

But only the timing table is not complete as the person assigned to fill it was off today.

That's why I'm late filling it.

I want to send the .sql file to you. How to do this ??

 

Update: Please use this link to view my .sql dumb file:

http://barbary.info/barbary.online/database.sql

Edited December 11, 2018 by Mahmoud
Added some action items

[Mahmoud]

I would like here to express my deep gratitude to Barand who is helping me like I never imagined.

 

[Barand]

Given that you have this the algorithm I came up with is this

Create master array of Phds and shuffle
Create master array of employes and shuffle

copy master phd array to temp phd array
copy master emp array to temp emp array

From schedule table create an array keyed by datetime whose elements are an array of the places with that same time  EG
"datetimeval1" => [   [placeA, phd, emp],
                      [placeB, phd, emp],
                      [placeC, phd, emp],

                  ],
"datetimeval2" => [   [placeX, phd, emp],
                      [placeY, phd, emp],
                      [placeY, phd, emp],

                  ]
                  
foreach schedArray as time => tdata
    foreach tdata as place
        if tempEmp is empty recopy master array to it
        pop value from tempEmp and update place[emp]
        ... repeat for phd data ...
    endforeach
endforeach

So we allocate the next emp to each time/place in turn. When all staff allocated reload staff array and continue.

Having the datetime as the key avoids the problem of double-allocation of staff (provided there are more avaulable staff than there are concurrent exams)

The array ca then be used to update the schedule table with the staff.