比较两个mysql表中的两列来查找不同的值

I have two table as

我有两张桌子

Employee table

id  username    status
1   user1       retire
2   user2       retire
3   user3       working
4   user4       working
5   user5       working
6   user6       working
7   user7       retire

Report Table

id  username    task    date
1   user3       task1   2018-02-15
2   user3       task2   2018-02-15
3   user3       task3   2018-02-15
4   user4       task1   2018-02-15
5   user4       task2   2018-02-15
6   user4       task3   2018-02-15
7   user4       task1   2018-02-16
8   user5       task1   2018-02-16
9   user5       task2   2018-02-16
10  user5       task3   2018-02-16
11  user5       task4   2018-02-16
12  user3       task2   2018-02-16
13  user3       task3   2018-02-16

What I have to do is, first select username from employee table those have status working and compare them with username from report table for particular date. I used left outer join for the same, it gave me result but not exactly what I intended, my query for outer join is

我要做的是,首先从员工表中选择有状态工作的用户名,并将其与特定日期的报表中的用户名进行比较。我使用左外连接相同,它给了我结果但不完全是我的意图,我对外连接的查询是

SELECT DISTINCT employee.username FROM employee LEFT OUTER JOIN report     
ON employee.username = report.username WHERE report.username IS null And     
employee.status = 'working'

I want to add further condition of dates from report table, but I am not able to get it, can someone suggest me how can I get the required result.

我想从报告表中添加日期的更多条件,但我无法得到它,有人可以建议我如何获得所需的结果。

4 个解决方案

#1

SELECT Distinct(employee.username) FROM employee where employee.status = 'working' 
and employee.username not in (SELECT report.username from report where 
report.date = 'YourDate')

#2

Something like this perhaps.

或许这样的事情。

Select * from ReportTable 
where taskDate between @minValue and @maxValue
and userName in (select username from Employee where status = 'working')

#3

Your question seems to be incomplete, still i try to answer as far as i understand this. It looks you try to add the date filter in the where clause but you had used REPORT TABLE "LEFT OUTER JOIN" with EMPLOYEE TABLE. It is good practice not to use where clause conditions to filter the records from LEFT OUTER JOINed Table, instead you can use that condition in ON clause (where do you use the LEFT OUTER JOIN)

你的问题似乎不完整,但据我所知,我还是试着回答。看起来你试图在where子句中添加日期过滤器,但是你已经使用了EMPLOYEE TABLE的REPORT TABLE“LEFT OUTER JOIN”。最好不要使用where子句条件来过滤LEFT OUTER JOINed Table中的记录,而是可以在ON子句中使用该条件(在哪里使用LEFT OUTER JOIN)

Regards,

Ananda Kumar J.

阿南达库马尔

#4

You have used LEFT OUTER JOIN wrong

您使用了LEFT OUTER JOIN错误

Mention the relationship between the tables

提到表之间的关系

SELECT DISTINCT employee.username FROM report 
LEFT OUTER JOIN employee ON employee.username = report.username
WHERE report.username IS null And employee.status = 'working'
and taskDate between @minValue and @maxValue

#1

SELECT Distinct(employee.username) FROM employee where employee.status = 'working' 
and employee.username not in (SELECT report.username from report where 
report.date = 'YourDate')

#2