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 = '建投')