0

Following is my database with 4 tables(Student, Course, Module, Instructor). And I wan to know whether this database is correct or not and if I am doing right how can I retrieve data from tables using student id as below.

  • What are the modules student 10122342 has? >>Expected answer: cn103, cn201

enter image description here

(pk) = primary key and (fk)=foreign key

The next thing is I wan to import some values from those above tables to a newly created table? the situation is I wan to import 's_id' and 'i_id' from above tables in 'id' attribute of newly crated table named "Everyone"; at the same time I also want to generate '0'/'1' value for 'type' attribute of "Everyone" table where 0 value for those id which are imported from 's_id' and 1 for rest of the ids imported from 'i_id'. Following is my expected new database table.

Everyone
id       |type |
-----------------
10122345 | 0   |
10122342 | 0   |
10222346 | 0   |
20432343 | 1   |
20432311 | 1   |
20532334 | 1   |

Please Can anyone help me? is it possible to do with mysql and php? THanks.

Himalay
  • 131
  • 2
  • 13
  • why do you want to do the above? what if student and instructer ids overlap? seems like with this table structure, each student only gets to enroll in one course. – dqhendricks May 14 '11 at 16:36
  • also, comma delimited values in a foriegn key is not good practice. how will you be able to use a join in a query to call this? if you ever have comma separated values in a table, it is a good sign that a separate table is needed. – dqhendricks May 14 '11 at 16:37
  • consider having separate tables that join tables together. for instance having a table that just contains a student id and a course id which would represent which students are taking which courses. this way you can have each student take multiple courses and each course can be taken by multiple students. – dqhendricks May 14 '11 at 16:40
  • Yes I understand that this is wrong. one student takes only one course and one course has multiple modules - here some of modules are on more than one course. This is the reason I am having trouble, I am expecting some kind of solution like String Compare or something to retrieve all the data. For now if i use the solution of @jeroen-offerijns from below Answer 1 it only shows one module that is assigned to only one course(cn103). – Himalay May 14 '11 at 18:01

1 Answers1

1

This should be the answer to your first question:

SELECT m_code FROM Module a INNER JOIN Student b ON a.c_code = b.c_code WHERE b.s_id = '10122342'

I can't come up with the answer to your second question, though it should be something with joins, this might help: MySQL Insert & Joins

Community
  • 1
  • 1
Jeroen
  • 13,056
  • 4
  • 42
  • 63