更新
我有3个表
图片 id , name
1 , abc
image_tags
id , image_id , tag
1 , 1345 , football
2 , 1345 , sport
3 , 1666 , football
image_categories
id , image_id , cat
1 , 1345 , 7
2 , 1345 , 3
3 , 1666 , 8
在我的查询中,我有一个标签数组我想获得类似的图像
这很容易(我只是写了这个,请不要提及语法错误)
select distinct(image_tags.image_id) , images.* from
image_tags join images on image_tags.image_id = images.id
where image_tags.tag IN (implode(',' , $tag_Array )) limit 10
但这是棘手的部分我不想要类别3的图片!如何在同一查询中过滤类别3的图像?
JOIN
表image_categories
然后你可以按类别cat
过滤:
SELECT DISTINCT
it.image_id,
i.*
FROM image_tags AS it
INNER JOIN images AS i ON it.image_id = i.id
INNER JOIN image_categories AS c ON c.image_id = i.id
WHERE it.tag IN (implode(',' , $tag_Array ))
AND c.cat <> 3
LIMIT 10;
在这里看到它的作用:
- SQL Fiddle Demo
更新
使用NOT IN
谓词或LEFT JOIN
:
Select distinct
it.image_id,
i.*
from image_tags AS it
INNER JOIN images AS i ON it.image_id = i.id
WHERE it.tag IN (implode(',' , $tag_Array ))
AND it.image_id NOT IN(SELECT image_id
FROM image_categories
WHERE image_id <> 3
AND image_id IS NOT NULL)
limit 10;
在这里看到它的作用:
- SQL Fiddle Demo