开发者

How do I fit these queries into just one?

开发者 https://www.devze.com 2023-03-23 11:24 出处:网络
Okay, I\'m going to try it again... this time I definitely got it right. Sorry about the confusion. :(

Okay, I'm going to try it again... this time I definitely got it right. Sorry about the confusion. :(

table1:

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

table2, table3, table4:

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

Here is my main query:

$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 now I want to integrate these 2 queries:

1st query:

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

ONLY if this statement t3.item_id NOT IN (...) in the above query is true, then t1.id from the main query should be displayed. However, if t3.item_id is IN (...) then it should be excluded from the results.

2nd query:

AN开发者_开发知识库D 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')

Same as with the 1st query.

Does this all fit into one query? I'd need it in one query only, so I know which results to show on the next page, e.g. LIMIT 5,5


I'm not sure I understand correctly but I think (NOT) EXISTS should be used:

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


apply the flow control flow structure in mysql

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE
0

精彩评论

暂无评论...
验证码 换一张
取 消