mysql三表關聯查詢練習

三張表：

1：公司表        

2、員工表

 3、中間關聯表

-- 關聯查詢方式查詢張飛在哪個公司
SELECT
  company.company_name AS '張飛公司' 
FROM
  company
  INNER JOIN com_emp ON com_emp.company_id = company.company_id
  INNER JOIN emp ON emp.emp_id = com_emp.emp_id 
WHERE
  emp.emp_name = '張飛';--   子查詢查詢方式查詢張飛在哪個公司
SELECT
  company.company_name 
FROM
  company 
WHERE
  company.company_id = ( SELECT com_emp.company_id FROM emp INNER JOIN com_emp ON emp.emp_name = '張飛' WHERE emp.emp_id = com_emp.emp_id ) --  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--  查詢建投公司的所有43歲以下員工的名字。
SELECT
  emp.emp_name 
FROM
  company
  INNER JOIN com_emp ON company.company_id = com_emp.company_id
  INNER JOIN emp ON com_emp.emp_id = emp.emp_id 
WHERE
  emp.`em_age` < '43' 
  AND company.company_name = '建投' ;
  
  -------------------------------------------------------------------------- 内連接配接等價于：
SELECT
  * 
FROM
  company,
  com_emp,
  emp 
WHERE
  company.company_id = com_emp.company_id 
  AND com_emp.emp_id = emp.emp_id 
  AND emp.`em_age` < '43' 
  AND company.company_name = '建投' 
  
  
  -- -------------------------------------------- 子查詢實作查詢建投公司的所有43歲以下員工的名字

SELECT emp.emp_name from emp where emp.emp_id=(
SELECT
  com_emp.emp_id
FROM
  company,
  com_emp 
WHERE
  company.company_id = com_emp.company_id 
  AND company.company_name = '建投')