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
JOIN
name
values and notid
values, we simply don't have enough information to properly/confidently answer this question. Please provide the previously requested EXPORT data (this needs to include theCREATE TABLE
andINSERT
queries for all relevant tables and just enough data to test with). I am going to vote to close this question as Unclear. If you update your question before it is closed, I'll retract my vote. A closed page can be reopened when it is ready.