too long

Question

I'm having a problem with a MySQL query that uses inner and left joins. The problem is that, where the foreign key in the primary table is blank in the case of a NULL-permitted field, the query doesn't read all the fields in the primary table.

My task is to organize a list of recordings with MySQL, some of which are live recordings while others are studio recordings. For the purposes of this question, I'm simplifying the table structure as follows:

table name: recordings
fields:
recording_id INT AUTO-INCREMENT
recording_name VARCHAR NOT NULL
artist_id INT NOT NULL FOREIGN KEY
event_id INT NULL FOREIGN KEY

table name: artists
fields:
artist_id INT AUTO-INCREMENT
artist_name VARCHAR NOT NULL

table name: events
fields:
event_id INT AUTO-INCREMENT
event VARCHAR NOT NULL
venue_id INT NOT NULL FOREIGN KEY

table_name: venues
fields:
venue_id INT AUTO-INCREMENT
venue_name VARCHAR NOT NULL
address VARCHAR NOT NULL

Where a recording was done live, I want the option to give details of the event where the recording was done, and if it was a studio recording, I leave the event field blank. In other words, in the recordings table event_id is an optional field, but artist_id is always required.

To edit an existing record in the recordings table, I have a form with three fields (again simplified):

<form>
  <input name="recording_name" type="text" value="<?php $recording_name ?>" />
  <select name="artist_id">
     <option>Select option</option>
     <option <?php $selected ?> value="1">Artist 1</option>
     etc.
  </select>
  <select name="event_id">
     <option>Select option</option>
     <option <?php $selected ?> value="1">Venue 1</option>
     etc.
  </select>  
</form>

I use the $selected variable to display the option corresponding with the existing value pulled from the database in the form's dropdown list, like so:

$selected = ($existing_value == $option_id ? 'selected="selected" : '');

Now, to get the existing values of the form I have the following SQL query:

$recording_sql = 
'SELECT * FROM recordings 
INNER JOIN artists ON recordings.artist_id = artists.artist_id 
LEFT JOIN events ON recordings.event_id = events.event_id
LEFT JOIN venues ON events.venue_id = venues.venue_id'

Then, to populate the two dropdowns:

$artist_sql = 
'SELECT * FROM artists'

$event_sql = 
SELECT * FROM events 
INNER JOIN venues ON events.venue_id = venues.venue_id

My PHP code looks something like this:

function buildForm($result){
  $data = $result->fetch_array($MYSQLI_ASSOC))
  $form = '<input name="recording_id" type="hidden" value="'.$data['recording_id'].'" />';
  $form .='<input name="recording_name" type="text" value="'.$data['recording_name'].'" />';
  $form .= buildSelectBox('artists', $data['artist_id']);
  $form .= buildSelectBox('events', $data['event_id']);
  return $form;
}

function buildSelectBox($table, $existing_id = NULL){
  //run SQL to pull data from relevant table (i.e. $artist_sql, or $event_sql which includes join to 'venues')
  //Loop through $mysqli_result to build each option
  while(etc....){
    $selected = ($existing_id == $option_id ? 'selected="selected" : '');
    $options_list .= '<option'.$selected.'value="'.$id.'">'.$artist_name.'</option>';
  }
  return $options_list;
}

This works fine if both foreign keys have values. However, when the event_id field is blank in recordings, it doesn't read the other foreign key either. It reads the text field, recording_name, fine though. In other words, the result set I get for $recordings_sql contains only the value of the recording_name field, while both foreign keys are returned blank, even though one is not blank. I've tried all the join permutations (left, inner, right) in different combinations, but none of them give the desired result.

I'm stumped! Thank you in advance for any help!

Answer 1

I spent most of yesterday trying to reproduce the problem with a simplified version of my application, and eventually I did. It turns out that the first foreign key (artist_id in my simplified example), was repeated in another table lower down in a series of joins that start with the second foreign key (event_id in my simplified example). So, my guess is that, if 'event_id' is null, 'artist_id' gets overwritten by a null value because the query will return empty values for everything after 'event_id' if it is empty. So, the problem isn't with how LEFT JOIN works, but rather with the repetition of the first foreign key in a subsidiary table that depends on a second foreign key that is allowed to be null. I evidently need to revisit my table structure...

Many thanks for all the suggestions!