1

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

  • 1
    When you ask a mysql question it is best to provide us with some EXPORTed data from phpMyAdmin. Can you give us some sample data from the relevant tables to develop/test with? This helps us to understand the table structures and their data types. – mickmackusa Oct 18 '18 at 14:40
  • I was able to see some CB tables on a colleague's site. Because you are relating custom fields and you are trying to JOIN name values and not id 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 the CREATE TABLE and INSERT 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. – mickmackusa Oct 19 '18 at 0:02
  • Hello Mick,thanks for replying and sorry for the delay in responding, I am still learning to navigate the site, so knowing when there is a reply wasn't that clear to me. I will gladly export some data from the table, where do I put the file? Sorry for what is probably a simple thing to do when one can see! Cheers, – Rebecca Oct 19 '18 at 16:05
  • 2
    I am in awe of your ability, and I not sure what is the easiest way (under the circumstances) for you to share your data. Typically, I tell people to go to phpMyAdmin, use the EXPORT feature, select the output as text (instead of a file), then copy-paste that text into the question body. If that is problematic, you can paste the text to pastebin.com and share a link. If there is any private/personal data, be sure to redact/modify/obfuscate it so that privacy is maintained. I'll assist in any way that I can, just ask. – mickmackusa Oct 19 '18 at 20:45
  • The StackExchange App has push alerts, so you can receive realtime notifications when a comment is directed to you or you receive a new answer. – mickmackusa Oct 20 '18 at 1:57
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.

  • Unfortunately, this page will not likely benefit future researchers because a complete question was never provided. Also, I believe I can help to refine your solution if you can offer enough of the data to recreate the task. Please help me to help you. – mickmackusa Nov 14 '18 at 3:47
  • I can imagine that this query returns the expected results when the logged in user is a team_leader but I can't imagine that it works for any other case. I wish to help but I require more sample table data than you have offered. – mickmackusa Nov 14 '18 at 4:18
  • Joining the comprofiler table to itself using the cb_service_name column from the first instance of the table and the cb_service_name column from the second instance of the table doesn't actually yield any benefit -- it will only negatively impact performance and bloat the chunk of data that the query is dealing with. If the available sql-sandbox sites don't offer a suitable interface for you, please reach out to someone who can get you though this sticky spot. I am eager to help you to refine your solution and avoid false positive results. – mickmackusa Nov 14 '18 at 4:18
  • 1
    There is a new Joomla Users Group Meetup that has formed in London. Are you close enough to attend one of their upcoming gatherings? They could help you to iron out your database exporting / querying issues and improve your project. I don't know if I should assume that you are Rebecca French from Bristol as data suggests. – mickmackusa Jan 4 at 23:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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