如何选择另一个表中不存在的行

I want to create a report.

我想创建一个报告。

I have 2 tables :

我有两张桌子:

student_list
student_list
transaction_list
transaction_list

Here's the rows included in those table

这是表中包含的行

student_list

Stud_ID | Name
1       | Cat
2       | Dog
3       | Rabbit

transaction_list

Trans_ID | Stud_ID | Payment_Month
1        | 1       | January
2        | 1       | February

Now I want to select data from student_list which exists in transaction_list and combine it with data from student_list which do not exist in transaction_list where Payment_Month = "January"

现在，我要从student_list中选择数据，它存在于transaction_list中，并将它与student_list中的数据结合起来，该列表不存在transaction_list中，Payment_Month = "January"

I have tried this query

我尝试过这个查询。

SELECT Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
  ON t.Stud_ID = s.Stud_ID
WHERE Payment_Month = "January"
UNION
SELECT Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
  ON t.Stud_ID = s.Stud_ID
WHERE t.Stud_ID IS NULL

And I got this

我有这

Name   | Trans_ID | Payment_Month
Cat    | 1        | January
Dog    | -        | -
Rabbit | -        | -

But when I change the Payment_Month value in the query to "March", I got this

但是当我将查询中的Payment_Month值更改为“March”时，我得到了这个

Name   | Trans_ID | Payment_Month
Dog    | -        | -
Rabbit | -        | -

Not as I want it, because I'd like this

不是我想要的，因为我喜欢这个

Name   | Trans_ID | Payment_Month
Cat    | -        | -
Dog    | -        | -
Rabbit | -        | -

Is there anyway I can get that?

我能得到这个吗?

4 个解决方案

#1

SELECT Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
  ON (t.Stud_ID = s.Stud_ID AND Payment_Month = "January")
UNION
SELECT Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
  ON t.Stud_ID = s.Stud_ID
WHERE t.Stud_ID IS NULL

Try it like this. By using WHERE you are filtering by the actual result your query delivers, so if your LEFT JOIN delivers null but you are filtering on that column the row is removed from the resultset.

这样尝试。通过使用查询所传递的实际结果进行筛选，因此，如果您的左连接传递null，但是您正在对该列进行筛选，那么行将从resultset中删除。

By putting the month into the JOIN condition it will just return null on this column (on the columns added by the JOIN) and keep the row in the resultset.

通过将月放入JOIN条件，它将在这一列(在JOIN添加的列上)返回null，并将行保存在resultset中。

#2

You are using an OUTER JOIN, so take advantage of it.

你使用的是外部连接，所以要利用它。

Outer joins return all of the rows on one side of the join regardless of whether there is a match or not.
外部连接返回连接一侧的所有行，而不管是否匹配。
Therefore, when you run a PREDICATE on the (outer) QUERY, you are eliminating the rows that would return the values you still want to see.
因此，在(外部)查询上运行谓词时，将消除返回您仍然希望看到的值的行。

You could do something like the following:

你可以这样做:

SELECT Name, B.Trans_ID, Stud_ID B.Payment_Month
FROM student_list A
LEFT OUTER JOIN (SELECT TRANS_ID, PAYMENT_MONTH, Stud_ID
                 FROM   transaction_list
                 WHERE  PAYMENT_MONTH = "January") B
ON A.Stud_ID = B.Stud_ID

It will return all the matches on ID where transaction_list is in January as well as the ones not matched in student_list. TO be fair, you might have been wanting to eliminate duplicates in your query, so you can stil accomplish this in the GROUP BY clause.

它将返回1月份transaction_list所在的ID上的所有匹配，以及student_list中不匹配的匹配。公平地说，您可能一直希望在查询中消除重复，因此您可以在GROUP BY子句中实现这一点。

#3

Try this;)

试试这个。

SELECT Name, COALESCE(Trans_ID, '-') AS Trans_ID, COALESCE(Payment_Month, '-') AS Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
ON t.Stud_ID = s.Stud_ID
AND NOT EXISTS (
    SELECT 1 FROM transaction_list tl WHERE tl.Stud_ID = t.Stud_ID AND tl.Payment_Month = 'January'
)

SQLFiddle DEMO HERE

SQLFiddle演示

#4

Thanks to @Philipp

由于@Philipp

This is the code that worked for me :

这是为我工作的代码:

SELECT Stud_Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
ON (t.Stud_ID = s.Stud_ID AND Payment_Month= "January")
UNION
SELECT Stud_Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
ON t.Stud_ID = s.Stud_ID
WHERE t.Stud_ID IS NULL

Even if I change "January" to "March", it worked as well as I wanted.

即使我把“一月”改成了“三月”，它的效果也和我想要的一样好。

#1

SELECT Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
  ON (t.Stud_ID = s.Stud_ID AND Payment_Month = "January")
UNION
SELECT Name, Trans_ID, Payment_Month
FROM student_list s
LEFT JOIN transaction_list t
  ON t.Stud_ID = s.Stud_ID
WHERE t.Stud_ID IS NULL