filter results

[btray77]

I have three tables.

keywordlist
-keyword
requiredkeyword
-keyword
negativekeyword
-keyword

I need to filter the data in the keywordlist not to show words that are in the negativekeyword table, and only show words that show in the requiredkeyword table. If either table is empty then don't worry about that table's constraints.

Here's what I've got so far:

SELECT keyword from Keywordlist LEFT OUTER JOIN NegativeKeyword ON Keywords.keyword=NegativeKeyword.keyword
WHERE NegativeKeyword.keyword IS NULL
UNION SELECT keyword FROM RequiredKeyword


1. Is the above SQL correct for this?
2. How would I go about doing this? I thought that Zeos could do SQL statements in filters, but did not like the "."'s...

Thanks for any help. (I'm new to all this)

-Brad

[btray77]

I have changed the SQL to the following which works in IB Expert, but how do I get it to work with Zeos.

Select *
From KeywordList 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
)

I've tried to put it in
1. zupdatesql refresh
2. zquery SQL

And no luck

Thanks

-Brad

[btray77]

Here's a demo of what I'm doing: http://uploading.com/files/cb34a4a3/filterprj.zip/

It's using Firebird 2.1.

-Brad

[trupka]

btray77,
It seems that you have some syntax errors in SQL (KEYWORD instead KEYWORDS).
Try this:

Code: Select all

Select *
From KeywordList 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
        )

[btray77]

I had caught that, I copied the SQL from the original project, and created the new demo project and when I made the tables for the demo project I types keywords instead of keyword for the field name. In the demo its not a syntax problem (I believe I fixed it)

Where would I place this code? It works if I run the IB Expert SQL Editor, but I'm not sure where to place it in the code. I've tried zupdatesql refresh and zquery SQL.

Thanks

-Brad

[trupka]

Sorry, I'm not quite sure what you are trying to do. I see problem like this:
select from KEYWORDLIST
everything that contains REQUIREDKEYWORD
and not contains any of the NEGATIVEKEYWORS

Am I right?

[btray77]

yes, your right.

-Brad