Re: Select an array from one databank and compare it to one from another databank

[Frank Olschewski]

Hey Barry,

thank you very much for your effort, really appreciate it!
But, I have to say, it does not really look easier, to be honest, it looks really difficult and hard to understand since I do need double as much tables, which are filled with numbers, which in turn have to be connected with each other with quite complicated JOIN commands :D
But I guess, this is the only way this can work, right?

I guess, I just have to test it out and see how it works, because I do not completely understand it yet - I get the point and see how they are linked with each other, but I currently imaginge a whole mountain of complicated rearrangement stuff with the tables, which I have to do now.

The sad part is, it actually worked with only one subject. So before, I had a table with requests with just subject instead of subject_id, lets say:

> `requests`(`request_id`,`street`,`class`,`subject`,
> `school`)
>  values (2,'Abbey Road','Performance','piano','Pop');

And just one table for the teachers:

> `teachers`(`teacher_id`,`teacher`,`subject`)
>  values (1,john,piano),
>  (1,john,piano),
>  (2,ringo,drums),
>  (3,paul,harmonica),
>  (4,george,guitar),

And I could use one simple join code:

mysql> SELECT
    -> r.street
    -> , r.class
    -> , r.subject
    -> , r.school
    -> FROM
    -> teachers t
    -> JOIN teachers
    -> ON requests.subject = teachers.subject
    -> WHERE teacher.teacher = '<logged in user>';

And it worked. For one subject per teacher.
Wouldn't it work, if I just take:

> `teachers`(`teacher_id`,`teacher`,`subject`)
>  values (1,john,piano),
>  (1,john,piano),
>  (2,ringo,drums),
>  (3,paul,harmonica),
>  (4,george,guitar),

and extend it with more subjects per teacher, which is actually what is done by the id-tables you proposed above?
Like:

> `teachers`(`teacher_id`,`teacher`,`subject`)
>  values (1,john,piano),
>  (1,john,piano),
>  (2,john,bass),
>  (3,ringo,drums),
>  (4,paul,harmonica),
>  (5,george,guitar),
>  (6,george,drums)

So now john and george are connected with two subjects - without using four tables and the complicated id-table, right? I guess no, but this is what I currently think ^^.

Best regards,
Frank