mysql select query from multiple tables returns duplicate results -

i new myysql , stack overflow if question little vauge let me know , provide more info.

i have written query gets data multiple tables isn't working how expected , stumped.

here code select students.student_fname, students.student_lname students, enrolments enrolments.courseid = 'c001';

but returns of students first , last names in students table , these names displayed twice.

here code 2 tables

create table students ( studentid char(10) not null, student_fname varchar(15) not null, student_lname varchar(15) not null, dob varchar(10) not null, constraint pk_students primary key (studentid) );

create table enrolments ( enrolmentno int not null auto_increment, studentid char(10) not null, courseid char(4) not null, constraint pk_enrolments primary key (enrolmentno), foreign key (studentid) references students (studentid), foreign key (courseid) references courses (courseid) )engine = innodb;

thanks

this because you've not defined how students relate enrolments.

you either need use inner join or add clause show how relate.

for example:

from students inner join enrolments on students.id = enrolments.studentid 

or

from students, enrolements  enrolments.studentid = students.id 

the first method newer , preferred many; legacy method supported.

to gain better understanding of table joins take @ excellent article: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

otherwise called cartesian product of 2 tables data relates data.

if not have unique index on enrolements (a student can have 1 class enrolment) select distinct field names or where... group fields limit results close you're looking well.

============================ in response follow-up comment===================

select s.student_fname, s.student_lname  students s  inner join enrolments  e   on s.studentid = e.studentid e.courseid = 'c001' group s.student_fname, s.student_lname order s.student_lname, s.student_fname 

the group eliminates duplicates same course. "on statement tells database how students relates enrolments. order provide reasonable order results.