Filtering Records From Database

Question

0.00/5 (No votes)

See more:

I have 4 tables in database

SQL

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.

SQL

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..

Posted 12-Dec-13 18:36pm

Hammad

Add a Solution

Comments

creepz03 13-Dec-13 1:44am

First of all, please stop using capital letters on each word you type.. its kinda hard to comprehend. And second, be more precise as to what you really want to do please?

Hammad 13-Dec-13 1:47am

Ok i want to show those rows from InformationData that do not contain any of the words from Filters in either their title or description columns

creepz03 13-Dec-13 2:14am

Ok, I'm going to clarify this.. You want to show all the data from InformationData that does not exist from the Filters(KeyWord)? Is that it?

Hammad 13-Dec-13 2:17am

I used the word 'contains' not 'exist' that why i use 'like' not '='

4 solutions

Solution 1

try this

SQL

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

Posted 12-Dec-13 19:05pm

MuhammadUSman1

Solution 2

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.

SQL

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

Posted 12-Dec-13 19:20pm

cigwork

Solution 4

It should actually be a much easier query if you have a more organize table relationship.
Try this..

SQL

select
	d.*
from InformationData d
where d.Title not in (select f.Keyword from Filters f inner join LUsersToFilters l on l.FilterId = f.Id where l.UserId=@userid)
	or d.Description not in (select f.Keyword from Filters f inner join LUsersToFilters l on l.FilterId = f.Id where l.UserId=@userid)

Posted 12-Dec-13 20:21pm

creepz03

Comments

Hammad 13-Dec-13 2:28am

Does not work for me it gives all records of the table not the filtered ones

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2013-12-12T20:37:00

Solution 5

Try this:

SQL

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)

Posted 12-Dec-13 20:37pm

Peter Leow

Comments

Hammad 13-Dec-13 2:46am

Work like a charm buddy have a nice day....

Peter Leow 13-Dec-13 2:49am

Great to know that it helps, you too have a nice day.

Maciej Los 13-Dec-13 2:52am

+5!

Peter Leow 13-Dec-13 3:10am

Thank you.