我有以下3个表结构(只列出所需列):
user_master
user_id(主键)
fname
employee
employee_id(主键)
user_id(外键)
estate
studexperience_master
exp_id(主键)
user_id(外键)
我有以下2个MySQL查询,从3个表中获取数据,并在表单中显示为1个表格(以下查询用于搜索结果)。前两个表能够正确地连接,但问题是,当连接下面两个表(即employee和studexperience_master)时,如果同一user_id出现3次,则获取的数据会重复,导致$result集合中出现3行而不是1行。我尝试了DISTINCT也加到第二个INNER JOIN上,但仍然无法解决问题,请问该如何编写查询语句?
$get_work=mysql_query("SELECT DISTINCT wm.work,wm.status FROM user_master um
INNER JOIN employee emp ON um.user_id =emp.user_id
INNER JOIN studexperience_master wm
ON emp.user_id=wm.user_id
WHERE wm.display='Y' $status_val $company_val $searchkind_val $location_val
$search_Qualification_val");
SELECT um.user_id,
um.fname,
um.lname,
um.email,
um.mobile,
emp.employee_id,
emp.ecountry,
emp.estate,
emp.ecity,
emp.prefcities,
emp.ca,
emp.cs,
emp.cwa,
emp.completed,
emp.persuing
FROM user_master um
INNER JOIN employee emp
ON um.user_id =emp.user_id
INNER JOIN studexperience_master wm
ON emp.user_id=wm.user_id
WHERE wm.display='Y' $status_val
$company_val $searchkind_val $location_val
$search_Qualification_val
而where子句的值属于像这样的值
WHERE wm.display='Y' AND status LIKE '%Fresher%' AND efamiliar LIKE '%Tally %' AND work LIKE 'Excise Audit' AND ecountry LIKE 'pune' OR ecity LIKE 'pune' OR estate LIKE 'pune' OR prefcities LIKE 'pune' AND ca REGEXP '%|CA CPT%' OR cs REGEXP '%|CA CPT%' OR cwa REGEXP '%|CA CPT%' OR completed REGEXP '%|CA CPT%' OR persuing REGEXP '%|CA CPT%'
用户主表
用户ID- 2
名字- Monika
电话- 2147...
电子邮件- a@a.b
员工
>employee_id- 1
>user_id- 2
>ecity- Amravati
>ca- CA IPCC Appeared
>cs- CS Executive Appeared
>cwa- CWA Inter Appeared
studexperience_master
ROW1
exp_id-1
user_id- 2
work- 税务审计(Tax AUDIT)
ROW2
exp_id-2
user_id- 2
work- 增值税审计(VAT AUDIT)