Jump to content

LEFT JOIN not displaying all ID's


Go to solution Solved by Moorcam,

Recommended Posts

Hi again,

I bet you guys have missed me. :D

I am trying to join two tables using LEFT JOIN to get the first name of a driver. This is kinda related to my previous topic about bootstrap modals.

I am using the following to join the tables:

                                        <?php
										$sql = "SELECT chtr.id, chtr.charter_name, chtr.fleet_number, chtr.driver, chtr.customer_name, chtr.customer_number, chtr.dep_date, chtr.status, usr.id, usr.fname
										FROM charters AS chtr
										LEFT JOIN users AS usr ON chtr.id = usr.id
										";
										if($result = mysqli_query($conn, $sql)){
											if(mysqli_num_rows($result) > 0){
												while($row = mysqli_fetch_array($result)){
										?>

However, it shows all data in the table except for chtr.id bar one, which does show.

I need a Guinness but I am not allowed anymore :(

Any help would be appreciated.

 

Link to comment
Share on other sites

Ok,

Changed the query a little as shown below. Shows all results now but output is multiplied by four.

										$sql = "SELECT *, usr.id, usr.fname
										FROM charters AS chtr
										LEFT OUTER JOIN users AS usr ON(chtr.id = usr.id) IS NOT NULL;
										";

 

Link to comment
Share on other sites

Which field in Charters identifies the driver in question? 

SELECT 
  chtr.id, chtr.charter_name, chtr.fleet_number, chtr.driver, chtr.customer_name, chtr.customer_number, chtr.dep_date, chtr.status
/*     \/                                             \____/    
       Which of these is the id into the users table? 
*/ 
, usr.id, usr.fname
FROM charters AS chtr
LEFT JOIN users AS usr
   ON chtr.id = usr.id  
/*         \/   
           Should this, perhaps, be chtr.driver? 
*/    

I would expect every table to have its own, unique id field and those ids are completely independent of one another (by which I mean Charter .id=6 is a completely different thing to Users .id=6). 

Regards, 
   Phill W. 

 

  • Great Answer 1
Link to comment
Share on other sites

1 minute ago, Phi11W said:

Which field in Charters identifies the driver in question? 

SELECT 
  chtr.id, chtr.charter_name, chtr.fleet_number, chtr.driver, chtr.customer_name, chtr.customer_number, chtr.dep_date, chtr.status
/*     \/                                             \____/    
       Which of these is the id into the users table? 
*/ 
, usr.id, usr.fname
FROM charters AS chtr
LEFT JOIN users AS usr
   ON chtr.id = usr.id  
/*         \/   
           Should this, perhaps, be chtr.driver? 
*/    

I would expect every table to have its own, unique id field and those ids are completely independent of one another (by which I mean Charter .id=6 is a completely different thing to Users .id=6). 

Regards, 
   Phill W. 

 

charter.driver contains the ID of the driver in question. Driver is selected from users table. Obviously a different ID and both tables have unique IDs

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.