How to construct a JOIN query with Joomla's query methods?

Question

I'm really new to SQL queries but now stuck with this one.

I need the first and last name of a user from table #__comprofiler where their cb_job_role = 'Team Leader' and their cb_service_name = [the cb_service_name of the currently logged in user].

Ultimately, it would be great if the result could then also be a hyperlink to the profile of the person in the result too.

I am using: Joomla! 3.8.13
MySQL 5.5.5-10 Community Builder

Any help would be much appreciated, please note I am blind and use a screen reader to access my computer so can't view images or screen shots either.

--- UPDATE 22/10/18 ---

Database export as text, all names are fictitious.

== Table structure for table traincomprofiler

|------ |Column|Type|Null|Default |------ |//id//|int(11)|No|0 |user_id|int(11)|No|0 |alias|varchar(150)|Yes|NULL |firstname|varchar(100)|Yes|NULL |middlename|varchar(100)|Yes|NULL |lastname|varchar(100)|Yes|NULL |hits|int(11)|No|0 |message_last_sent|datetime|No|0000-00-00 00:00:00 |message_number_sent|int(11)|No|0 |avatar|text|Yes|NULL |avatarapproved|tinyint(4)|No|1 |canvas|text|Yes|NULL |canvasapproved|tinyint(4)|No|1 |canvasposition|tinyint(4)|No|50 |approved|tinyint(4)|No|1 |confirmed|tinyint(4)|No|1 |lastupdatedate|datetime|No|0000-00-00 00:00:00 |registeripaddr|varchar(50)|No| |cbactivation|varchar(50)|No| |banned|tinyint(4)|No|0 |banneddate|datetime|Yes|NULL |unbanneddate|datetime|Yes|NULL |bannedby|int(11)|Yes|NULL |unbannedby|int(11)|Yes|NULL |bannedreason|mediumtext|Yes|NULL |acceptedterms|tinyint(1)|No|0 |invite_code|text|Yes|NULL |cb_role|text|Yes|NULL |cb_contract_type|text|Yes|NULL |cb_registration_type|tinyint(3)|Yes|NULL |cb_service_name|text|Yes|NULL |cb_team_leader|text|Yes|NULL |cb_support_workers|text|Yes|NULL |cb_locality_manager|text|Yes|NULL |cb_team_worker_child|text|Yes|NULL |cb_lhps_expiry_date|datetime|No|0000-00-00 00:00:00 |cb_test_field|mediumtext|Yes|NULL |cb_fp_course_date|text|Yes|NULL == Dumping data for table traincomprofiler

|------ |id|user_id|alias|firstname|middlename|lastname|hits|message_last_sent|message_number_sent|avatar|avatarapproved|canvas|canvasapproved|canvasposition|approved|confirmed|lastupdatedate|registeripaddr|cbactivation|banned|banneddate|unbanneddate|bannedby|unbannedby|bannedreason|acceptedterms|invite_code|cb_role|cb_contract_type|cb_registration_type|cb_service_name|cb_team_leader|cb_support_workers|cb_locality_manager|cb_team_worker_child|cb_lhps_expiry_date|cb_test_field|cb_fp_course_date |------ |501|501| |Joe| |Turner|0|0000-00-00 00:00:00|0|NULL|1|NULL|1|50|1|1|0000-00-00 00:00:00| | |0|NULL|NULL|NULL|NULL|NULL|0|NULL|support_child|contract|0|NULL|NULL|NULL|NULL|NULL|0000-00-00 00:00:00|NULL|0000-00-00 00:00:00 |502|502| |Hannah|Bud|Apple|0|0000-00-00 00:00:00|0|NULL|1|NULL|1|50|1|1|2018-10-10 08:59:24| | |0|NULL|NULL|NULL|NULL|NULL|0|NULL|support_child|bank|0|Blue House Bristol|Elizabeth Small|NULL|NULL|NULL|0000-00-00 00:00:00|NULL|0000-00-00 00:00:00 |503|503| |Thomas|Orange|Hope|0|0000-00-00 00:00:00|0|NULL|1|NULL|1|50|1|1|0000-00-00 00:00:00| | |0|NULL|NULL|NULL|NULL|NULL|0|NULL|team_leader_adults|contract|0|NULL|NULL|NULL|NULL|NULL|0000-00-00 00:00:00|NULL|0000-00-00 00:00:00 |504|504| |Rebecca|Jane|French|0|0000-00-00 00:00:00|0|NULL|1|NULL|1|50|1|1|0000-00-00 00:00:00| | |0|NULL|NULL|NULL|NULL|NULL|0|NULL|locality_manager|contract|0|NULL|NULL|NULL|NULL|NULL|0000-00-00 00:00:00|NULL|0000-00-00 00:00:00 |505|505| |Charlotte|Pink|Lady|0|0000-00-00 00:00:00|0|NULL|1|NULL|1|50|1|1|2018-10-10 14:08:25| | |0|NULL|NULL|NULL|NULL|NULL|0|NULL|learning_development_team|contract|0|Blue House Bristol| |[{"cb_team_worker_child":""},{"cb_team_worker_child":""}]|Rebecca French|NULL|0000-00-00 00:00:00|NULL|0000-00-00 00:00:00 |507|507| |Blue House| |Bristol|0|0000-00-00 00:00:00|0|NULL|1|NULL|1|50|1|1|0000-00-00 00:00:00| | |0|NULL|NULL|NULL|NULL|NULL|0|NULL|services|contract|1|NULL|NULL|NULL|NULL|NULL|0000-00-00 00:00:00|NULL|0000-00-00 00:00:00

Answer 1

Eventually I managed to figure it out and the resulting code is as follows, perhaps it will help someone else in the future if they require a similar solution.

select t2.id, CONCAT( " ", t2.firstname, t2.lastname) as whole_name
FROM #__comprofiler as t1, #__comprofiler as t2
where t1.cb_service_name = t2.cb_service_name
and t2.cb_role = "team_leader"
    and t1.user_id = [user_id] 

Getting the results to link to the profile of the respective user was done by using a "column_" link.

Many thanks to all those who offered their help with this, much appreciated.