选择多个连接表中的位置

I have the logic worked out, just not sure how to best write this query.

我有逻辑解决，只是不知道如何最好地编写此查询。

the logic is

逻辑是

we have a deal ID of 1
a deal is linked to multiple regions
a deal is linked to multiple interests
a user is linked to multiple regions
a user is linked to multiple interests
we want all users where....

我们的交易ID为1，交易链接到多个区域，交易链接到多个利益，用户链接到多个区域，用户链接到多个利益，我们希望所有用户在哪里....

the user is linked to the same region as a deal

用户链接到与交易相同的区域

userRegionLink url, dealRegionLink drl
url.regionId is in drl.regionId where drl.dealId = 1
the user is linked to the same interest as a deal
userInterestLink uil, dealInterestLink dil
uil.interestId is in dil.interestId where dil.dealId = 1
this would give us a list of the users
now we need to select distinct from the list so we only end up sending each user a single email

userRegionLink url，dealRegionLink drl url.regionId位于drl.regionId，其中drl.dealId = 1，用户链接到与交易userInterestLink uil相同的兴趣，dealInterestLink dil uil.interestId位于dil.interestId，其中dil.dealId = 1我们现在需要从列表中选择不同的用户列表，这样我们最终只会向每个用户发送一封电子邮件

But I have no idea what the best way to write this query would be.

但我不知道写这个查询的最佳方法是什么。

We are dealing with a few tables here

我们在这里处理几张桌子

We have

我们有

users which has all the user Information in it userId and other columns not important

其中包含userId中所有用户信息的用户和其他不重要的列

userInterestLink which has userId and interestId
dealInterestLink which has dealId and interestId

userInterestLink，具有userId和interestId，具有dealId和interestId的dealInterestLink

userRegionLink which has userId and regionId
dealRegionLink which has dealId and regionId

userRegionLink，其userId和regionId dealRegionLink具有dealId和regionId

so what we are wanting in the end is all the user info which matches.

所以我们最终想要的是所有匹配的用户信息。

3 个解决方案

#1

I take RC's answer and modify it

我接受RC的回答并对其进行修改

SELECT u.userId, uil.interestId, url.regionId FROM users u 
  JOIN userInterestLink uil ON (uil.userId = u.userId) 
  JOIN userRegionLink url ON (url.userId = u.userId) 
WHERE interestId IN (
  SELECT DISTINCT interestId FROM dealInterestLink WHERE dealId = 1
) AND regionId IN (
  SELECT DISTINCT regionId FROM dealRegionLink WHERE dealId = 1
)

as there is no need for LEFT JOIN if I exclude the NULL rows afterwards.

因为如果我之后排除NULL行，则不需要LEFT JOIN。

A more "symmetric" version without subqueries and with USING would be

没有子查询和USING的更“对称”的版本将是

SELECT u.userId, uil.interestId, url.regionId FROM users u 
  JOIN userInterestLink uil USING (userId)
  JOIN userRegionLink url USING (userId)
  JOIN dealInterestLink dil USING (interestId)
  JOIN dealRegionLink drl USING (regionId, dealId)
WHERE dealId = 1

Untested as well.

未经测试。

#2

Something like:

就像是：

SELECT u.userId, uil.interestId, url.regionId FROM users u 
  LEFT JOIN userInterestLink uil ON (uil.userId = u.userId) 
  LEFT JOIN userRegionLink url ON (url.userId = u.userId) 
WHERE uil.interestId IS NOT NULL AND uil.interestId IN (
  SELECT DISTINCT interestId FROM dealInterestLink WHERE dealId = 1
) AND url.regionId IS NOT NULL AND url.regionId IN (
  SELECT DISTINCT regionId FROM dealRegionLink WHERE dealId = 1
)

? If result is OK, you can then SELECT DISTINCT u.userId FROM users u -- ...

？如果结果没问题，那么你可以选择SELECT DISTINCT u.userId FROM users u - ...

(not tested)

（未测试）

#3

SELECT `u`.*
  FROM `users` AS `u`
  JOIN `userRegionLink` `userReg` USING ( `userId` )
  JOIN `userInterestLink` `userInt` USING ( `userId` )
  JOIN `dealInterestLink` `dealInt` USING ( `interestId` )
  JOIN `dealRegionLink` `dealReg` USING ( `regionId` )
  JOIN `deal` `d` ON ( `dealInt`.`dealId` && `dealReg`.`dealId` && `d`.`dealId` = 1 )
GROUP BY `u`.`userId`

Tested locally using dummy data and presumed schema. Worked OK.

使用虚拟数据和假定的模式在本地测试。工作得好。

#1