Jump to content
Mahmoud

Update MySQL table from 2 or 3 other tables

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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:

image.png.9a2a4acbe8408e2512633efd081d151c.png

Roles:

image.png.6c95139f3224c47b09f669110825c51e.png

Places:

image.png.7c622f54f57352f1ea1b18bfd10ed603.png

Thanks and really appreciated.

 

Share this post


Link to post
Share on other sites

Is the objective

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

The last table to be filled is like this:

image.png.7d4577d3b2376dcdbc49875cdf92f035.png

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

:)

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

This is only an example for the completed table.

Share this post


Link to post
Share on other sites
3 hours ago, Mahmoud said:

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

Zzzzzzzzzzzz! Are you manually typing all the the data ?

Share this post


Link to post
Share on other sites
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 by Mahmoud
Added some action items

Share this post


Link to post
Share on other sites

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

:)

 

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.