selecting where in multiple join tables

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 开发者_JAVA百科linked to multiple interests

we want all users where....

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

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

userInterestLink which has userId and interestId

dealInterestLink which has dealId and interestId

userRegionLink which has userId and regionId

dealRegionLink which has dealId and regionId

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

I take RC's answer and modify it

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.

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

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.

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 -- ...

(not tested)

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.