modifying my JOIN statement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
abionifade
Forum Commoner
Posts: 34
Joined: Thu Apr 18, 2002 5:32 pm

modifying my JOIN statement

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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
User avatar
abionifade
Forum Commoner
Posts: 34
Joined: Thu Apr 18, 2002 5:32 pm

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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
User avatar
abionifade
Forum Commoner
Posts: 34
Joined: Thu Apr 18, 2002 5:32 pm

Post 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')
Post Reply