从表1中获取记录，并在表2值不存在时从另一个表中连接它

1st Table - explore_offers:

第一张表 - explore_offers：

- id
- Primary Key - offer_unique

2nd Table - participated_explore_offers:

第二张表 - visited_explore_offers：

- id
- email - user_email
- Primary Key - offer_unique

What i want: * Show 1st Table records, and exclude those records, which are in the 2nd table with a specific email found

我想要的是：*显示第一个表记录，并排除那些在第二个表中找到特定电子邮件的记录

ex:

例如：

SELECT eo.*
     , peo.user_email 
  FROM explore_offers eo 
  LEFT 
  JOIN participated_explore_offers peo 
    ON eo.offer_unique = peo.offer_unique
 WHERE peo.user_email = 'test@gmail.com'

I've tried that example, and i'm getting 0 records. I have 2 records in the first table, and one in the second table, and the result i want is:

我试过这个例子，我得到0条记录。我在第一个表中有2条记录，在第二个表中有一条记录，我想要的结果是：

*. get that one record from the first table, where this record does NOT exist in the second table.

*。从第一个表中获取该记录，其中第二个表中不存在此记录。

1st Table content:

第1表内容：

Nr id  Primary Key
1  0   m1
2  1   m2

2nd Table Content

第二表内容

Nr id user_email      Primary Key
1  0  test@gmail.com  m1
1  0  test2@gmail.com  m2

Expected

预期

Nr id Primary Key
1  1  m2

What i had:

我有什么：

0 Records

0记录

2 个解决方案

#1

SQL DEMO

Try this :

尝试这个：

select * from explore_offers
where offer_unique not in 
(select offer_unique from participated_explore_offers where user_email='test@gmail.com')

#2

Move the email filteration to the JOIN condition to make it work with LEFT JOIN:

将电子邮件过滤移动到JOIN条件以使其与LEFT JOIN一起使用：

SELECT eo.*,peo.user_email 
FROM explore_offers eo 
LEFT JOIN participated_explore_offers peo ON (eo.offer_unique = peo.offer_unique) 
                                          AND peo.user_email = 'test@gmail.com'
WHERE peo.user_email is null;

demo:

演示：

| Nr | id | offer_unique | user_email |
|----|----|--------------|------------|
|  2 |  1 |           m2 |     (null) |

#1