当表2值不存在时，从表1中获取记录，并从另一个表中将其联接 [英] Get records from table 1 and join it from another table when the table 2 value does not exist

问题描述

第一张表-Explore_offers:

1st Table - explore_offers:

- id
- Primary Key - offer_unique

第二张表-参与调查的报价:

2nd Table - participated_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

例如:

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 = '[e-mail]'

我已经尝试过该示例，并且我得到了0条记录. 我在第一张表中有2条记录，在第二张表中有一条，我想要的结果是:

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:

*.从第一个表中获得一条记录，而该记录在第二个表中不存在.

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

第一表内容:

Nr id  Primary Key
1  0   m1
2  1   m2

第二张桌子的内容

Nr id user_email      Primary Key
1  0  [e-mail]  m1
1  0  [e-mail]  m2

预期

Nr id Primary Key
1  1  m2

我所拥有的:

0条记录

推荐答案

SQL演示

尝试一下:

select * from explore_offers
where offer_unique not in 
(select offer_unique from participated_explore_offers where user_email='[e-mail]')

这篇关于当表2值不存在时，从表1中获取记录，并从另一个表中将其联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！