复杂的MySQL连接与动态where - Complex MySQL join with dynamic where

Complex MySQL join with dynamic where

本文关键字：动态 where 连接 MySQL 复杂 | 更新日期: 2023-09-27

这是我的两个表，我想加入…

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

我想从"notifications"表中返回成员为"testa"的行，并且"position"表中的contentType等于"1"。因此，对于上面的表数据，我想从"notifications"返回的前两行，因为"Email"的contentType在位置表中是允许的。

这可能与我的表结构吗?

试试这个:

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       ║
╚═════════╩══════════╩═════════╩═════════════╝

查看SQLFiddle

尝试:

select n.*
from notifications n
join position p 
  on n.member = p.member and
     case n.contentType when 'Email' then p.getsEmail 
                        when 'SMS' then p.getsSMS end = 1
where n.member = 'testera'

SQLFiddle。

这里可以定义内容类型为email或sms

$contenttype="Email";
$query="select * from notification,position where position.member='testera' and position.gets".$contenttype."='1' ";