Hello!
I am trying to build a database which will consist of 3 tables and many more Queries which will randomly allocate police officers to the required , for the day, duties.
I am in a police department in Athens Greece which has more than 250 officers and currently there are 8 people trying to allocate everyone daily according to a book they keep everyone on.
Now what i did is i created 3 tables as you can see and the problem I face is that the appending query i want to use, so each and every police offer is allocated randomly to each post, gets multiplied by the data that the tableb has and does not combine them to give me the results i want
More specifically we have :
```
TABLEA
ID | POLICE RANK | FULL NAME |
____________________________________
288066 | Const. | Chris Meli |
273111 | Serg. | John Do |
231444 | Const. | Bill Park |
298432 | Const. | Joe Park |
_____________________________________
```
which contains the info of the police officers and is connected to the ID field in TableC so even from the connection on TableA you can examine the duties every officer has been assigned to the previous days.
```
TABLEB
DUTY | Number of Police needed |
| for each service |
____________________________________
Patrol | 1 |
Guards| 1 |
Courts | 2 |
____________________________________
```
I put the number 1 and 2 just for the sake of simplicity. Normally TableA will contain *more than 250 people* and on *TableB will be many Duties and the number of police needed will vary depending on the date and many other factors.
```
TABLEC
ID | DUTY | DATE |
____________________________________
.......|.................| ................|
.......|.................| ................|
.........|.................| ................|
.........|.................| ................|
_____________________________________
```
TableC will be populated From TableA (ID),TableB (Duty) and an input for the date i will be scheduling with the following appending query
```
INSERT INTO TABLEC ( DUTY, DATE, ID )
SELECT TABLEB.DUTY, [INPUT DATE], TABLEA.ID
FROM TABLEA, TABLEB INNER JOIN n ON n.n <= TABLEB.[Number of Police needed for each service];
```
(n is a numbers table and n.n is a column that has like 10000 numbers so don't pay attention to that)
Now the appending query returns me the results i need but what happens is , it kind of multiplies the position needed with the officer's ID . So instead of having this:
```
TABLEC
ID | DUTY | DATE |
____________________________________
288066 | Patrol | 23/06/2019 |
273111 | Guards | 23/06/2019 |
231444 | Courts | 23/06/2019 |
298432 | Courts | 23/06/2019 |
_____________________________________
```
I have this:
```
TABLEC
ID | DUTY | DATE |
____________________________________
288066 | Patrol | 23/06/2019 |
288066 | Guards | 23/06/2019 |
288066 | Courts | 23/06/2019 |
288066 | Courts | 23/06/2019 |
273111 | Patrol | 23/06/2019 |
273111 | Guards | 23/06/2019 |
273111 | Courts | 23/06/2019 |
273111 | Courts | 23/06/2019 |
231444 | Patrol | 23/06/2019 |
231444 | Guards | 23/06/2019 |
231444 | Courts | 23/06/2019 |
231444 | Courts | 23/06/2019 |
298432 | Patrol | 23/06/2019 |
298432 | Guards | 23/06/2019 |
298432 | Courts | 23/06/2019 |
298432 | Courts | 23/06/2019 |
_____________________________________
```
Is there a way to connect TableA.ID and TableB.DUTY without being multiplied automatically?at
I appreciate any input. I asked the same question at stackoverflow and someone prompted me to search for inner and left joins. I have looked everywhere but still cant figure it out!
Now the question is how to specify it in a join type? :/
I am trying to build a database which will consist of 3 tables and many more Queries which will randomly allocate police officers to the required , for the day, duties.
I am in a police department in Athens Greece which has more than 250 officers and currently there are 8 people trying to allocate everyone daily according to a book they keep everyone on.
Now what i did is i created 3 tables as you can see and the problem I face is that the appending query i want to use, so each and every police offer is allocated randomly to each post, gets multiplied by the data that the tableb has and does not combine them to give me the results i want
More specifically we have :
```
TABLEA
ID | POLICE RANK | FULL NAME |
____________________________________
288066 | Const. | Chris Meli |
273111 | Serg. | John Do |
231444 | Const. | Bill Park |
298432 | Const. | Joe Park |
_____________________________________
```
which contains the info of the police officers and is connected to the ID field in TableC so even from the connection on TableA you can examine the duties every officer has been assigned to the previous days.
```
TABLEB
DUTY | Number of Police needed |
| for each service |
____________________________________
Patrol | 1 |
Guards| 1 |
Courts | 2 |
____________________________________
```
I put the number 1 and 2 just for the sake of simplicity. Normally TableA will contain *more than 250 people* and on *TableB will be many Duties and the number of police needed will vary depending on the date and many other factors.
```
TABLEC
ID | DUTY | DATE |
____________________________________
.......|.................| ................|
.......|.................| ................|
.........|.................| ................|
.........|.................| ................|
_____________________________________
```
TableC will be populated From TableA (ID),TableB (Duty) and an input for the date i will be scheduling with the following appending query
```
INSERT INTO TABLEC ( DUTY, DATE, ID )
SELECT TABLEB.DUTY, [INPUT DATE], TABLEA.ID
FROM TABLEA, TABLEB INNER JOIN n ON n.n <= TABLEB.[Number of Police needed for each service];
```
(n is a numbers table and n.n is a column that has like 10000 numbers so don't pay attention to that)
Now the appending query returns me the results i need but what happens is , it kind of multiplies the position needed with the officer's ID . So instead of having this:
```
TABLEC
ID | DUTY | DATE |
____________________________________
288066 | Patrol | 23/06/2019 |
273111 | Guards | 23/06/2019 |
231444 | Courts | 23/06/2019 |
298432 | Courts | 23/06/2019 |
_____________________________________
```
I have this:
```
TABLEC
ID | DUTY | DATE |
____________________________________
288066 | Patrol | 23/06/2019 |
288066 | Guards | 23/06/2019 |
288066 | Courts | 23/06/2019 |
288066 | Courts | 23/06/2019 |
273111 | Patrol | 23/06/2019 |
273111 | Guards | 23/06/2019 |
273111 | Courts | 23/06/2019 |
273111 | Courts | 23/06/2019 |
231444 | Patrol | 23/06/2019 |
231444 | Guards | 23/06/2019 |
231444 | Courts | 23/06/2019 |
231444 | Courts | 23/06/2019 |
298432 | Patrol | 23/06/2019 |
298432 | Guards | 23/06/2019 |
298432 | Courts | 23/06/2019 |
298432 | Courts | 23/06/2019 |
_____________________________________
```
Is there a way to connect TableA.ID and TableB.DUTY without being multiplied automatically?at
I appreciate any input. I asked the same question at stackoverflow and someone prompted me to search for inner and left joins. I have looked everywhere but still cant figure it out!
Now the question is how to specify it in a join type? :/