query optimization

[jd22]

Hello

I am trying to do something but seems i stuck.

I have a mysql query but i guess it need optimization.

 

I really appreciate for any help

 

Regards

 

$QUERY = "	SELECT * 
			FROM student s 
			LEFT JOIN address a ON s.Postcode = a.Postcode";

$res = mysql_query($QUERY, MyConn()) or die(mysql_error());
while($row = mysql_fetch_array($res))
{
	$SQL2 = "SELECT * FROM fenrolment f LEFT JOIN Course c ON f.courseid = c.courseid WHERE  f.studentid = '" .$row['StudentID']. "'";

	$SQL3 = "SELECT * FROM enroll e LEFT JOIN SCourse s ON e.SCourseID = s.SCourseID WHERE e.studentid = '" .$row['StudentID']. "'";

	echo $SQL2 ." < -- >". $SQL3 ."<BR>";  // Debug Purpose only
}

[MasterACE14]

I'm assuming what you currently has works? As far as optimisations are concerned, you should specify the fields being selected, instead of using an asterisk to select everything.

[jd22]

Dear MasterACE14

Thanks for reply

I need all fields. However as you can see i am getting students name and then try to find which course they are taking in a loop.

I want to do that with a single query.

 

Also another question is: with that query i am taking everything. But some students are taking 3 or 4 classes  and that query brings me same student 4 times (with different classes) How can i distinct only one class if a student takes 4 classes.

 

I hope i made myself clear...

 

Regards

[fenway]

It's not optimized because you're not using JOINs.

[jd22]

It's not optimized because you're not using JOINs.

Dear Fenway

That is the point. how can i merge those 3 queries into one. and use distinct for any of table in those queries but student table.

 

Regards

[fenway]

Explain that distinct requirement again...?

[sniperscope]

Explain that distinct requirement again...?

Dear Fenway

Thank you for your interest.

After Googling  for a while i came up with this query

 

$QUERY = " SELECT *

FROM `sdb`.`Student` s

INNER JOIN addr a ON s.zipcode = a.zipcode

INNER JOIN enrol en ON s.SID = en.SID

INNER JOIN SCourse sc ON en.SCourseID = sc.SCourseID

INNER JOIN course c ON sc.CourseID = c.CourseID

INNER JOIN fenrol fe ON c.CourseID = fe.CourseID WHERE s.SName = '$SName' AND c.CourseID='$CourseID' AND sc.WeekDay='$WeekDay'";

 

My student table has one to many relation with course table

Some student can get lesson twice or trice in a week and if i run query above then i got 3 records(if student takes tree course in a week) at same name, same address same age same group

How can i distinct course (i just want to take only one course name. Student may take only one course in a week or takes 100 course in a week it doesn't matter)

 

Lord... i am almost forgetting English.

 

If my English hard to understand please tell me.

 

Regards

[kickstart]

Hi

 

To limit it to a single row you need to chose a particular course for each row (possibly the MAX course) and use a suitable aggregate function and GROUP BY.

 

All the best

 

Keith

[sniperscope]

Hi

 

To limit it to a single row you need to chose a particular course for each row (possibly the MAX course) and use a suitable aggregate function and GROUP BY.

 

All the best

 

Keith

Dear Keith

 

If you don't mind may i have an example 

Please

[kickstart]

Hi

 

Not sure of all the field names (and depending on exactly what you want back there may well be a better way to do this), but as an example something like this should do it.

 

SELECT *
FROM `sdb`.`Student` s
INNER JOIN addr a ON s.zipcode = a.zipcode
INNER JOIN (SELECT SID, MAX(SCourseID) AS MaxCourseID FROM enrol GROUP BY SID) Deriv1 ON Deriv1.SID = s.SID 
INNER JOIN enrol en ON Deriv1.SID = en.SID AND Deriv1.MaxCourseID = en.sCourseID
INNER JOIN SCourse sc ON en.SCourseID = sc.SCourseID
INNER JOIN course c ON sc.CourseID = c.CourseID
INNER JOIN fenrol fe ON c.CourseID = fe.CourseID 
WHERE s.SName = '$SName' AND c.CourseID='$CourseID' AND sc.WeekDay='$WeekDay'";

 

By the way, generally I would say do not use SELECT *

 

All the best

 

Keith