php - 复杂的 MySQL 加入动态 where

Question

这是我要加入的两个表...

    name: notifications
-------------------------------------------
content | position | member  | contentType
-------------------------------------------
test a  | manager  | testera | Email
test b  | manager  | testera | Email
test c  | manager  | testera | SMS
-------------------------------------------

    name: position
-----------------------------------------------
position  |  member  |  getsEmail  |  getsSMS
-----------------------------------------------
manager   |  testera |  1          |  0
employee  |  testerb |  0          |  1
-----------------------------------------------

我想返回成员为“testera”的“通知”表中的行，并且“位置”表中的内容类型等于“1”。所以对于上面的表数据，我想从返回的“notifications”中获取前两行，因为位置表中允许使用“Email”的contentType。

这可能与我的表结构有关吗？

Answer 1

试试这个:

SELECT n.* 
  FROM notifications n
  JOIN position p
    ON n.member = p.member
 WHERE (n.contentType = 'Email' AND p.getsEmail = 1)
    OR (n.contentType = 'SMS' AND p.getsSMS = 1)

结果:

╔═════════╦══════════╦═════════╦═════════════╗
║ CONTENT ║ POSITION ║ MEMBER  ║ CONTENTTYPE ║
╠═════════╬══════════╬═════════╬═════════════╣
║ test a  ║ manager  ║ testera ║ Email       ║
║ test b  ║ manager  ║ testera ║ Email       ║
╚═════════╩══════════╩═════════╩═════════════╝

See this SQLFiddle

SQLFiddle with more data