Page 1 of 1
modifying my JOIN statement
Posted: Thu May 30, 2002 8:35 am
by abionifade
Hi guys,
Any idea as to how I can modify my JOIN statement to
include another table called
chosen_module of which i'd like to select the field name
module_title (from the chosen_module table) where student_id is also equal to $newvar?
Code: Select all
$query = mysql_query("select * from student_table left join module_table on student_table.course_id = module_table.course_id where student_table.user_id='$newvar'");
Many thanks for your help with this
-Abi
Posted: Thu May 30, 2002 1:14 pm
by mikeq
Hi,
I will make the assumption that the table chosen_module links Students to Modules, as a student can have 1 or many modulesand a module can be taken by 1 or many students, correct? I would also like to think that there is a table called Course, or are you repeating course details for all the possible modules a course can contain? i.e. Course_ID, Course_Title, Module_ID, Module_Title...etc
So top of the head database structure would be
table Course
COURSE_ID, TITLE
table Module
MODULE_ID,COURSE_ID,TITLE
table Student (this makes an assumption a student can only do 1 course)
STUDENT_ID,FIRSTNAME,SURNAME,COURSE_ID
table Chosen_Module
STUDENT_ID,MODULE_ID
This structure would be in at least Third Normal Form.
select S.FIRSTNAME, S.SURNAME, C.TITLE, M.TITLE
from Student S, Course C, Module M, Chosen_Module CM
where S.COURSE_ID = C.COURSE_ID and
S.STUDENT_ID = CM.STUDENT_ID and
CM.MODULE_ID = M.MODULE_ID and
S.STUDENT_ID = '$newvar'
This query also assumes that every Student has a course etc, if not you will need to change it to use LEFT/RIGHT joins.
Hope this helps
Mike
Posted: Fri May 31, 2002 5:38 am
by abionifade
Hi Mike,
thanks for getting back to me..
I have tried this join statement
Code: Select all
SELECT *
FROM student_table LEFT JOIN module_table ON student_table.course_id = module_table.course_id
LEFT JOIN chosen_module ON chosen_module.user_id = student_table.user_id
WHERE student_table.user_id='$newvar'
I am pulling the field name "module_title" from two different tables and populating them into one drop down form.
The two tables are chosen_module and module_table respectively.
When i apply the JOIN that you gave me, the values from the chosen_module table override the contents of the dropdown displaying just module_titles in the chosen_module table.
Even more perculiar is that the values repeat themselves. So for instance, if i have 4 records in the chosen_module table, the 4 records 'each' appear 6 times in the dropdown field.
I have noticed that the number of times that these records repeat is the same number as the total number of records in the module_table.
As another example, if i now have 7 records in the module_table, the values in the drop-down (displaying just the module_titles from chosen_module) will now repeat 7 times, still assuming that only 4 records are in chosenn_module.
I hope i havent confused you at all here, what i am striving for is to display all records (module_title) from both tables in that dropdown simultaneously.
Really appreciate your help in this, its one of the very last things that i have to do in my project.
I can post some mysqldumps of my database structure on here with sample data if you would like?
Many, many thanks for your help
Posted: Fri May 31, 2002 12:31 pm
by mikeq
Hi,
Did you follow my comments on the database structure? If it doesn't look like this then you will probably have problems getting the query you want, or was I way off the mark regarding the relationship of the data?
Post your DB structure and sample data.
Mike
Posted: Fri May 31, 2002 2:03 pm
by abionifade
Hi Mike, thank you so much for helping me out with this.
here are the 4 dumps along with sample data for each table.
Please do let me know if u'd like me to create some more same data.
CREATE TABLE chosen_module (
user_id varchar(20) NOT NULL default '',
module_title varchar(255) NOT NULL default ''
)
insert into chosen_module
(user_id, module_title) values
('ch150', 'Systems')
insert into chosen_module
(user_id, module_title) values
('ch150', 'Management')
insert into chosen_module
(user_id, module_title) values
('ch150', 'Sociology')
CREATE TABLE module_table (
module_id varchar(6) NOT NULL default '',
lecturer_id varchar(5) NOT NULL default '',
module_title varchar(50) default NULL,
course_id varchar(6) NOT NULL default '',
UNIQUE KEY module_id (module_id)
)
insert into module_table
(module_id, lecturer_id, module_title, course_id) values
('XX210', 'wjob', 'technology', 'GHEE')
CREATE TABLE options_table (
module_id varchar(200) NOT NULL default '',
module_title varchar(200) NOT NULL default '',
course_id varchar(200) NOT NULL default '',
UNIQUE KEY module_id (module_id)
)
insert into options_table
(module_id, module_title, course_id) values
('FVG5', 'Systems', 'GHEE')
insert into options_table
(module_id, module_title, course_id) values
('YH32', 'Management', 'GHEE')
insert into options_table
(module_id, module_title, course_id) values
('ED56', 'Sociology', 'GHEE')
CREATE TABLE student_table (
user_id varchar(5) NOT NULL default '',
first_name varchar(40) NOT NULL default '',
last_name varchar(40) NOT NULL default '',
course_id varchar(8) NOT NULL default '',
PRIMARY KEY (user_id),
UNIQUE KEY user_id (user_id)
)
insert into student_table
(user_id, first_name, last_name, course_id) values
('ch150', 'abi', 'onifade', 'GHEE')