mysql - 我如何将这些查询放入一个查询中？

Question

好吧，我再试一次……这次我绝对做对了。抱歉造成困惑。 :(

表 1:

+----+---------+------------+------+
| id | item_id | product_id | type |
+----+---------+------------+------+

表2、表3、表4:

+----+---------+
| id | item_id |
+----+---------+

这是我的主要查询:

$sql = "SELECT t1.* FROM table1 AS t1, table2 AS t2 WHERE t2.id = '1' AND 
t2.item_id = t1.item_id AND t1.type NOT IN ('type1', 'type2') LIMIT 5";

$a = mysql_query($sql);
while($b = mysql_fetch_assoc($a))

现在我想整合这两个查询:

第一个查询:

AND IF t1.type = 'type3' THEN

SELECT t3.item_id FROM table3 AS t3 WHERE t3.id = t1.product_id AND
t3.item_id NOT IN (SELECT t2.item_id FROM table2 AS t2 WHERE t2.id = '1')

只有当上述查询中的语句 t3.item_id NOT IN (...) 为真时，主查询t1.id/strong> 应该显示。但是，如果 t3.item_id 是 IN (...)，那么它应该从结果中排除。

第二个查询:

AND IF t1.type = 'type4' THEN

SELECT t4.item_id FROM table4 AS t4 WHERE t4.id = t1.product_id AND
t4.item_id NOT IN (SELECT t2.item_id FROM table2 AS t2 WHERE t2.id = '1')

与第一个查询相同。

这一切都适合一个查询吗？我只在一个查询中需要它，所以我知道在下一页上显示哪些结果，例如限制 5,5

Answer 1

我不确定我是否理解正确，但我认为应该使用 (NOT) EXISTS:

SELECT t1.* 
FROM table1 AS t1
  JOIN table2 AS t2 
    ON t2.item_id = t1.item_id
WHERE t2.id = '1' 
  AND t1.type NOT IN ('type1', 'type2')
  AND NOT ( t1.type = 'type3' 
            AND EXISTS 
                ( SELECT t3.item_id
                  FROM table3 AS t3
                  WHERE t3.id = t1.product_id
                    AND t3.item_id NOT IN
                          ( SELECT t2.item_id 
                            FROM table2 AS t2
                            WHERE t2.id = '1' )
                )
           ) 
  AND NOT ( t1.type = 'type4' 
            AND EXISTS 
                ( SELECT t4.item_id
                  FROM table4 AS t4
                  WHERE t4.id = t1.product_id
                    AND t4.item_id NOT IN
                          ( SELECT t2.item_id 
                            FROM table2 AS t2
                            WHERE t2.id = '1' )
                )
           ) 
ORDER BY WhatYouWant
LIMIT x,y