SQL Filter Multiple Tables Data

Question

If it matters, I'm using Firebird 2.1 database.

I have three tables, one with keywords, one with negative keywords, and the other with required keywords. I need to be able to filter the data so the output has just the keywords that meat the stipulation of not being in the negative keyword list, and IF there are any required words, then it will require the results to have those keywords in the end result.

The tables are very similar, the field in the tables that I would be matching against are all called keyword.

I don't know SQL very well at all. I'm guessing it would be something like SELECT keyword from keywordstable where keyword in requiredkeywordstable and where NOT in negativekeywordstable

Just a side note, The required keywords table could be empty which would mean there are no required keywords.

Any help would be appreciated.

Example Of Tables:

KeywordsTable -Keywords varchar 255 RequiredKeywordsTable -Keywords varchar 255 NegativeKeywordsTable -Keywords varchar 255

Example Data: KeywordsTable Cat Dog Mouse Horse House

With Nothing set in the Negative and Required Keywords Tables then the output would simply be the Keywords Table data unchanged.

IF RequiredKeywordsTable has the value of Car, Cat, Dog then the output would be and Cat Dog

If NegativeKeywordsTable has the value of Horse and requiredkeywords was empty then the output of the Keywords table would be cat, dog, mouse, House.

etc..

-Brad

Answer 1

Your specification is a bit hazy. It would help if you provided some schema. Is the keywords table just words or is it a list of keywords for a given entity? What happens if there exists at least one RequiredKeyword but not all Keywords are required? Should the non-required keywords show or should only required keywords show in that scenario? If both required and non-required keywords should be returned, then how does the list of required keywords affect the outcome? Here are some possible solutions:

Scenario 1:

1. The three tables are keywords for a given entity key.
2. The EntityKey is not nullable.
3. If a given entity has a required keyword, then only required keywords should show.

Select ...
From Keywords As K
    Left Join NegativeKeywords As NK
        On NK.EntityKey = K.EntityKey
    Left Join RequiredKeywords As RK
        On RK.EntityKey = K.EntityKey
Where NK.EntityKey Is Null
    And (
        Not Exists  (
                    Select 1
                    From RequiredKeywords As RK1
                    Where RK1.EntityKey = K.EntityKey
                    )
        Or RK.EntityKey Is Not Null
        )

Scenario 2:

1. Only the Keywords table is for a given entity key or is just words but the other two are a list of required and negative keywords.
2. The Keyword column in all three tables is not nullable.
3. If there exists even one required keyword, then only required keywords should show:

Select ...
From Keywords As K
    Left Join NegativeKeywords As NK
        On NK.Keyword = K.Keyword
    Left Join RequiredKeywords As RK
        On RK.Keyword = K.Keyword
Where NK.Keyword Is Null
    And (
        Not Exists  (
                    Select 1
                    From RequiredKeywords As RK1
                    Where RK1.Keyword = K.Keyword
                    )
        Or RK.Keyword Is Not Null
        )

Scenario 3:

1. The Keywords table is just words
2. The Keyword column in all three tables is not nullable.
3. The system should return whether the given keyword is required or not but should also show non-required keywords.

Select ...
    , Case When RK.Keywords Is Not Null Then 1 Else 0 End As IsRequired
From Keywords As K
    Left Join NegativeKeywords As NK
        On NK.Keyword = K.Keyword
    Left Join RequiredKeywords As RK
        On RK.Keyword = K.Keyword
Where NK.Keyword Is Null

Addition

Given your additional information, here is how you can solve the problem. First, based on what you said, I'm presuming the schema looks something like:

Create Table Keywords( Keywords varchar(255) not null primary key )
Create Table NegativeKeywords( Keywords varchar(255) not null primary key  )
Create Table RequiredKeywords( Keywords varchar(255) not null primary key  )

If the Keywords column is the only column, I would make it not nullable and the primary key. This ensures that you do not have duplicates and lets us rely on the fact that the column is not nullable to check for non-existence. The problem is significantly more difficult to solve if the Keywords column is nullable in the NegativeKeywords and/or RequiredKeywords table.

Insert Keywords(Keywords) Values( 'Cat' )
Insert Keywords(Keywords) Values( 'Dog' )
Insert Keywords(Keywords) Values( 'Mouse' )
Insert Keywords(Keywords) Values( 'Horse' )
Insert Keywords(Keywords) Values( 'House' )

Select ...
From Keywords As K
    Left Join NegativeKeywords As NK
        On NK.Keywords = K.Keywords 
    Left Join RequiredKeywords As RK
        On RK.Keywords = K.Keywords 
Where NK.Keywords Is Null
    And (
        Not Exists  (
                    Select 1
                    From RequiredKeywords As RK1
                    Where RK1.Keywords = K.Keywords
                    )
        Or RK.Keywords Is Not Null
        )