Problem with a simple query script used in RS Forms on Joomla 4

Question

I am trying to pre populate a Joomla website form (RSForm!Pro) with information already entered by a registered user into the extension Event Booking by Ossolution Team.

The RSForm!Pro allows pre population of online form fields with scripts called when form is opened. I have a simple query working as expected when it pulls a first name as follows;

//<code>
$user = JFactory::getUser();
$db = JFactory::getDbo();
$userId = $user->get('id');

// Is the user logged in?
if ($userId) { 
// Grab the value from the database. 
$db->setQuery("SELECT `last_name` FROM `#__eb_registrants` WHERE `user_id`='".$userId."'"); return $db->loadResult();
}
//</code>

This is OK as the info is all held in the eb_registrants table which has columns including ‘user_id’ - which is the ID assigned by the Joomla core when the user is registered, so the first script has no problem identifying the logged in user.

Here’s the eb-registrants table showing the Event Booking extension assigned ‘id’ and the Joomla core assigned ‘user_id’.

Screen shot of eb_registrants

The next field I wish to pre populate is for the users mobile phone/cell number, which is held in a different table named eb_field_values with the field id of 17.

I got so far as the following script which does not return anything;

//<code> 

$user = JFactory::getUser();
$db = JFactory::getDbo();
$userId = $user->get('id');

// Is the user logged in?
if ($userId) {
    // Grab the value from the database.
    $db->setQuery("SELECT * FROM `#__eb_field_values` WHERE `registrant_id`='" . $userId . "' AND `field_id` = '17'");
    return $db->loadResult();
}

//</code>

I have realised that the problem is that the table eb_field_values (see screenshot below) references the extension assigned ‘registrant_id’ rather than the Joomla assigned ‘id’ - see the earlier screenshot above.

Screen shot of eb_field_values

So to call the mobile number (field_id = 17) for a user, the script has to look for the Joomla ‘user_id’ in the eb_registrants table and then use the Event Booking assigned ‘id’ from this table to call values from the ‘eb_field_values’ table.

Putting this into a script is beyond my elementary skills. Although I know this is probably simple I'm a bit like Homer at the control panel hitting random buttons.

Steve

UPDATE

With lots of help including a very welcome comment from FiddlingAway on this question the final script is as follows;

//<code>
$user = JFactory::getUser();
$db = JFactory::getDbo();
$userId = $user->get('id');

// Is the user logged in?
if ($userId) {
// Grab the value from the database.
$db->setQuery("SELECT field_value FROM #__eb_registrants AS R
              JOIN #__eb_field_values AS FV ON R.id = FV.registrant_id
              WHERE R.user_id = " . $userId . "
              AND FV.field_id = '17'");

return $db->loadResult();
}
//</code>