连接三个mysql表/两个select语句

Question

我有以下表格：

http://www.gulllakeschools.net/mysqltables.pdf

我需要以以下格式提取数据：学生姓、学生名、日期事件、日期事件（学生登录的每一天都有一个日期事件）

组件com_users是我需要从组件列中提取的组件。

我是mysql的菜鸟，不知道如何一次完成。我有这些单独运行良好的精选语句:

SELECT civicrm_contact.last_name, civicrm_contact.first_name, civicrm_uf_match.uf_id
FROM civicrm_contact, civicrm_uf_match
WHERE civicrm_contact.id = civicrm_uf_match.contact_id

SELECT user_id, component, group_concat( date_event )
FROM jos_content_statistics
WHERE component = "com_users"
GROUP BY user_id

但我不能为我的生活让他们加入。我尝试过加入，加入，完全加入，合并，并且都会产生错误。我已经尝试了在 where 语句之前和之后的连接。联合生成排序规则错误。我没有尝试过内部连接，因为我无法弄清楚。

Answer 1

试试这个：

SELECT cc.last_name, cc.first_name, cm.uf_id, jc.component, jc.dateevent 
FROM civicrm_contact cc
INNER JOIN civicrm_uf_match cm ON cc.civicrm_contact.id = cm.contact_id
INNER JOIN
(
    SELECT user_id, component, group_concat( date_event ) dateevent
    FROM jos_content_statistics
    WHERE component = "com_users"
    GROUP BY user_id, component
) jc ON cm.uf_id = jc.user_id

Answer 2

SELECT 
  civicrm_contact.last_name, 
  civicrm_contact.first_name, 
  civicrm_uf_match.uf_id,
  (select group_concat(jos_content_statistics.date_event) 
   from jos_content_statistics 
   where user_id = civicrm_uf_match.uf_id 
     and civicrm_uf_match.contact_id = civicrm_contact.id 
     and component = "com_users" 
   GROUP BY user_id) as date_events
FROM civicrm_contact join civicrm_uf_match
on civicrm_contact.id = civicrm_uf_match.contact_id
where civicrm_contact.contact_sub_type='student' 

Answer 3

SELECT C.last_name, C.first_name, S.date_event
FROM civicrm_contact as C
INNER JOIN civicrm_uf_match as M on M.contact_id = C.id 
INNER JOIN jos_content_statistics as S on M.uf_id = S.user_id