从数据库过滤记录 [英] Filtering Records From Database

问题描述

我在数据库中有4个表

  1 。用户列（Id，电子邮件，密码，活动）
  2 。 InformationData列（Id，Title，Description，Link）
  3 。过滤列（ID，关键字，有效）
  4 。 LUserToFilter列（UserId，FilterId）

现在我想要的是从InformationData表中过滤特定记录，其中标题和描述不包含来自过滤器表的关键字我们从LUserToFilter表中选择那些基于特定用户ID的过滤器。



我尝试了这个查询但它只返回那些包含过滤关键字的记录并且不返回其他记录。

 创建 过程 [dbo]。[SP_GetFilteredData] 
  @ userid   int  
  as  
 开始 
 选择 D. * 来自过滤器f 
 内部 < span class =code-keyword> join  LUsersToFilters L.  on  L.FilterId = F.Id 
  inner   join  InformationData D  on  D.Title  like  ' ％' + F.KeyWord + ' ％' 或 D.Description 喜欢  ' ％' + F.KeyWord + '  ％' 
 其中 L.UserId=@userid 
  end  

任何建议请..

解决方案

试试这个

 创建 程序 [dbo]。[SP_GetFil teredData] 
  @ userid   int  
  as  
 开始 
 选择 D. * 来自过滤器f 
 内部  join  LUsersToFilters L  on  L.FilterId = F.Id 
  inner   join  InformationData D  on  D.Title  not   / *  添加不* /  喜欢  ' ％' + F.KeyWord + '  ％' 或 D.Description  not not   / *   add not * /   like  ' ％' + F.KeyWord + ' ％' 
 其中 L.UserId=@userid 
  end  

试试这个：

  SELECT  *  FROM  InformationData 
  WHERE   NOT   EXISTS  
（ SELECT  *  FROM 过滤器 INNER   JOIN  LUserToFilter  ON  Filters.id = LUserToFilter.FilterId  WHERE （In formationData.Title  LIKE  CONCAT（' ％'，Filters.KeyWord，' ％'）或 InformationData.Description  LIKE  CONCAT（' ％' ，Filters.KeyWord，' ％'）） AND  LUserToFilter.userid =  @ userid ）

认为解决方案1需要如果您只对关键字没有出现在说明或标题中的描述感兴趣，请修改如下。





< pre lang =SQL> - 不在标题中而不是 in description
内部 join [InformationData]
不 [InformationData]。[标题] 喜欢 ' ％' + [Filters]。[Keyword] + ' ％'
和 不 [InformationData]。[描述] 喜欢 ' ％' + [Filters]。[Keyword] + ' ％'

I have 4 tables in database

1. Users Columns(Id, Email, Password, Active)
    2. InformationData Columns(Id, Title, Description, Link)
    3. Filters Columns(Id, Keyword, Active)
    4. LUserToFilter Columns(UserId, FilterId)

Now What I Want Is To Filter Specific Records From InformationData Table In Which Title And Description Does Not Contains Keywords From Filters Table And We Select Those Filters From LUserToFilter Table Based On A Specific User Id.

I Tried This Query But It Only Returns Those Records Which Contains That Filterd Keywords And Not Return The Other Records.

Create Procedure [dbo].[SP_GetFilteredData]
    @userid int
    as
    begin
    select D.* from Filters f
    inner join LUsersToFilters L on L.FilterId=F.Id
    inner join InformationData D on D.Title like '%'+F.KeyWord+'%' or D.Description like '%'+F.KeyWord+'%'
    where L.UserId=@userid
    end

Any Suggestion Please..

解决方案

try this

Create Procedure [dbo].[SP_GetFilteredData]
    @userid int
    as
    begin
    select D.* from Filters f
    inner join LUsersToFilters L on L.FilterId=F.Id
    inner join InformationData D on D.Title not /*add not*/  like '%'+F.KeyWord+'%' or D.Description not/*add not*/ like '%'+F.KeyWord+'%'
    where L.UserId=@userid
    end

Try this:

SELECT *  FROM InformationData
WHERE NOT EXISTS
(SELECT * FROM Filters INNER JOIN LUserToFilter ON Filters.id=LUserToFilter.FilterId WHERE (InformationData.Title LIKE CONCAT('%',Filters.KeyWord,'%')  OR InformationData.Description LIKE CONCAT('%',Filters.KeyWord,'%')) AND LUserToFilter.userid = @userid)

Think solution 1 needs to be modified as below if you are only interested in descriptions where keywords do not appear in either the description or the title.

--  not in title and not in description
inner join [InformationData]
      on   not [InformationData].[Title] like '%'+[Filters].[Keyword]+'%'
      and  not [InformationData].[Description] like '%'+[Filters].[Keyword]+'%'

这篇关于从数据库过滤记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！