I am trying to LEFT JOIN a table, but the two columns used for relating rows are not identically formatted.

My problem is the value in a.vendor, it has leading zeros on it, and c.No doesn't have leading zeros. I tried to make it int to remove the zero, but my code int('a.vendor') is not working.

    ->select($db->quoteName('c.Name', 'vendor_name'))
    ->select($db->quoteName('d.name', 'vendor_subcontractor'))
    ->from($db->quoteName('cert_pack_fcm', 'a'))
    ->join('LEFT', $db->quoteName('our_vendors', 'c') . ' ON ' . $db->quoteName('a.vendor' )  . ' = ' . $db->quoteName('c.No'))

What should I do?

  • Did my edit damage the question requirements? Do you want trim the leading zeros while joining, or in the SELECT, or both? Commented May 25, 2022 at 10:29
  • Yes it is, I need our_vendors table to track the vendor name on it because i only have the id of the vendor on the cert_pack_fcm. so to get the name i name to connect it with our_vendors. but the problem is i just want to remove leading zeros on it. Commented May 25, 2022 at 17:13

2 Answers 2


There are several ways you can do this. First, you already have the correct value in c.No so you can just select it instead of a.vendor:

$query->select(['a.*', 'c.No as vendor']);

Using this method you might want to declare specific columns to prevent unnecessary data (i.e. a.vendor) from being sent:

$query->select(['a.column1', 'a.column2', 'c.No as vendor']);

Another method is to cast the column to integer:

$query->select(['a.*', $query->castAs('INT', 'a.vendor') . ' AS vendor'])

Same advice about declaring columns applies here.

Third method is to cast the value in PHP after the data has been retrieved:

$results = $db->loadObjectList();

foreach ($results as $row)
    $row->vendor = (int) $row->vendor;
  • I interpreted the requirement to be casting in the LEFT JOIN. Or maybe you are right. Commented May 25, 2022 at 10:33

I am assuming that your concern is regarding the join of the our_vendors table. One viable, raw SQL technique is to explicitly trim zeros from the front of each value with the SQL TRIM() function.

TRIM(LEADING '0' FROM myfield)

Alternatively, as shown in @Sharky's answer, you can use a Joomla-native approach (castAs()) which converts the zero padded string to an integer (which by definition cannot have leading zeros).

None of your table names or column names pose any risk to query stability or security, so it is actually perfectly safe to remove all of those quoteName() method calls which make the query building script longer and harder to read. This is more of a personal preference than official advice. For developers that are uncertain about sql stability/security, there is no harm in quoting everything.

$query = $db->getQuery(true);  // must be declared so that chaining can re-reference $query for castAs() method
    ->select('a.*, c.Name AS vendor_name, d.name AS vendor_subcontractor')
    ->from('cert_pack_fcm AS a')
    ->leftJoin('our_vendors AS c ON ' . $query->castAs('INT', 'a.vendor') . ' = c.No')

Note: see Issues to be aware of when calling concatenate(), regexp(), rand(), and castAsChar() in sql queries regarding declaring $query so that its methods can be used while chaining the rest of the query building methods.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.