|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Left Join Pains!
I am trying to build a MySQL query to show all of
the rows in the unit_control table. Lets say there are 30 rows in the table, I need to display them all to the user. In the browser I want to display a table with 2 cols and as many rows as are in the unit_control table. The left col will be the information from the unit_control table and in the right col will either be the session owners score from the student_unit_history table, or some if_null() text. Either way I want to display all of the rows from my unit_control table in the browser. The code below only gets me the rows where the sessions owner matches his records in the student_unit_history table. So if he has taken 2 units, I get 2 rows displayed in the browser, when I need all 30! Can I do what I am trying to do? Don //////////////////// //My query $qryUnits = "SELECT unit_control.*, student_unit_history.* "; $qryUnits .= "FROM unit_control "; $qryUnits .= "LEFT JOIN student_unit_history "; $qryUnits .= "ON unit_control.uc_unit_code = student_unit_history.unit_id "; $qryUnits .= "WHERE student_unit_history.student_id = '$sess_user_name' "; $qryUnits .= "ORDER BY unit_control.uc_unit_code"; ////////////////// // Rest of the code //////////////// // First Query if (!($resultUnits = mysql_query($qryUnits, $dblink1))) echo("$dbFailedConnect <br>" . mysql_error()); //////////////// /////////////// // first query results while($listScore = mysql_fetch_assoc($resultUnits)) { ////////////// //Unit control Data $unit_code = $listScore["uc_unit_code"]; $unit_possible_score = $listScore["uc_possible_score"]; ////////////////////////////// // Student unit history data $student_unit_id = $listScore["unit_id"]; $student_id = $listScore["student_id"]; $student_grade = $listScore["suh_unit_grade"]; $student_pending = $listScore["suh_pending"]; if (is_null($student_grade)) { $score_col = "Not yet taken"; } else { $score_col = "$student_grade out of $unit_possible_score"; } echo("$unit_code, $student_id, $score_col<br>"); } ///////////////////////////// //The tables in question: TABLE unit_control ( uc_unit_code varchar(6) NOT NULL , uc_unit_name varchar(25) NOT NULL , uc_unit_date date NOT NULL DEFAULT '0000-00-00' , uc_unit_level char(1) NOT NULL , uc_content_area varchar(5) NOT NULL , uc_num_of_items int(3) unsigned NOT NULL DEFAULT '1' , uc_possible_score int(3) unsigned NOT NULL DEFAULT '0' , uc_unit_detail text , UNIQUE KEY uc_unit_code (uc_unit_code), KEY uc_unit_code_2 (uc_unit_code) ) TYPE=MyISAM; TABLE student_unit_history ( id int(8) NOT NULL auto_increment, student_id varchar(24) NOT NULL , unit_id varchar(7) NOT NULL , suh_unit_grade varchar(5) , suh_pending int(1) unsigned , PRIMARY KEY (id) ) TYPE=MyISAM; |
|
#2
|
|||
|
|||
|
RE: Left Join Pains!
Could you give me some sample data and an example of what you would like displayed from a query?
|
![]() |
| Viewing: Codewalkers Forums > Other Technologies > Database Help > Left Join Pains! |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|