返回带有特定标签的行 [英] return rows with specific tag

问题描述

我有3张桌子

列表 列表标签 listing_listtag

listings listtags listing_listtag

列表可以分配有多个标签，这些标签存储在listing_listtag中，带有指向列表和listtags表的预输入键

listing can have multiple tags assigned to it and those are stored in listing_listtag with forein keys to listings and listtags table

我在选择仅带有2个标签的列表时遇到问题.

I have a problem to select only a listing which has 2 tags associated with it.

我有这个查询:

select *
from `listtags`
    inner join `listing_listtag` on `listtags`.`id` = `listing_listtag`.`tag_id`
where `listing_listtag`.`listing_id` = 5
   and `slug` = "delivery"
   AND slug = "wireless-internet" 

，它什么也不返回.如果我更改最后一个AND或OR现在返回

and it returns nothing. if I chnage the last AND to OR it now returns

我希望查询只返回listing_id为5的一行，我该怎么做?

I would like the query to only return one row where listing_id is 5, how can I do this?

推荐答案

这里是一种方法:

select ?.listing_id
from listtags lt inner join
     listing_listtag llt
     on lt.id = llt.tag_id
where llt.listing_id = 5 and
      ?.slug in ('delivery', 'wireless-internet')
group by ?.listing_id
having count(*) = 2;

?用于带有列的表的表别名.

The ? is for the table alias of the table with the column.

注意:如果数据可以重复，请使用count(distinct slug).

Note: If the data can have duplicates, use count(distinct slug).

这篇关于返回带有特定标签的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！