How do I fit these queries into just one?
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
精彩评论