CODEWITHSUNDEEP
sqlsql-servert-sql
xxyyxx
xxyyxx

Reputation: 2396

How would I switch this from performing an AND operation to an OR?

Right now this query searches a table called article for entries who's title and abstract field's contain a certain keyword. The set it returns are articles who's title AND abstract contain the keyword, but I would like to change it so it returns articles who's title OR abstract contains the keyword. How would I accomplish this? By simply changing the inner joins to an outer?

BEGIN
    with articlesearch as (
    SELECT top 1000 FT_TBL.articleID, FT_TBL.title,FT_TBL.abstract,FT_TBL.publicationdate,
        (select j.journalID from journal  j where FT_TBL.journalID=j.journalID) as sourceID,
        (select j.journalname from journal  j where FT_TBL.journalID=j.journalID) as sourcename,
        (select j2.medabbr from journal j2 where FT_TBL.journalID=j2.journalID) as medabbr,
        (select j1.impactfactor from journal  j1 where FT_TBL.journalID=j1.journalID) as impactfactor,
        KEY_TBL.RANK,
        ROW_NUMBER() OVER (ORDER BY KEY_TBL.RANK desc) AS RowNumber
    FROM article AS FT_TBL 
        INNER JOIN 
            CONTAINSTABLE(article,title,@keyword) AS KEY_TBL
            ON FT_TBL.articleID = KEY_TBL.[KEY]
        INNER JOIN
            CONTAINSTABLE(article,abstract,@keyword) AS KEY_TBL2
            ON FT_TBL.articleID = KEY_TBL2.[KEY]
    where  FT_TBL.inactive=0
    ORDER BY RANK DESC
    )

    SELECT articleID, sourcename,title,abstract,publicationdate,medabbr  
    FROM articlesearch
    WHERE RowNumber BETWEEN @RowStart AND @RowEnd ORDER BY publicationdate desc;

END

Upvotes: 0

Views: 73

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The following version changes the inner joins to left outer joins and adds a where clause to get what you want:

with articlesearch as (
SELECT top 1000 FT_TBL.articleID, FT_TBL.title,FT_TBL.abstract,FT_TBL.publicationdate,
    (select j.journalID from journal  j where FT_TBL.journalID=j.journalID) as sourceID,
    (select j.journalname from journal  j where FT_TBL.journalID=j.journalID) as sourcename,
    (select j2.medabbr from journal j2 where FT_TBL.journalID=j2.journalID) as medabbr,
    (select j1.impactfactor from journal  j1 where FT_TBL.journalID=j1.journalID) as impactfactor,
    KEY_TBL.RANK,
    ROW_NUMBER() OVER (ORDER BY KEY_TBL.RANK desc) AS RowNumber
FROM article AS FT_TBL 
    left outer JOIN 
        CONTAINSTABLE(article,title,@keyword) AS KEY_TBL
        ON FT_TBL.articleID = KEY_TBL.[KEY]
    left outer join 
        CONTAINSTABLE(article,abstract,@keyword) AS KEY_TBL2
        ON FT_TBL.articleID = KEY_TBL2.[KEY]
where FT_TBL.inactive=0 and (key_tbl.[key] is not null or key_tbl2.[key] is not null)
ORDER BY RANK DESC
)

SELECT articleID, sourcename,title,abstract,publicationdate,medabbr  
FROM articlesearch
WHERE RowNumber BETWEEN @RowStart AND @RowEnd ORDER BY publicationdate desc;

Upvotes: 2

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28718

You'll need to change the INNER JOIN to an OUTER, as DigitalD notes, but then you also need to filter your results where at least one of the joins isn't empty.

-- snip
----
-- /snip
FROM article AS FT_TBL 
    LEFT OUTER JOIN 
        CONTAINSTABLE(article,title,@keyword) AS KEY_TBL
        ON FT_TBL.articleID = KEY_TBL.[KEY]
    LEFT OUTER JOIN
        CONTAINSTABLE(article,abstract,@keyword) AS KEY_TBL2
        ON FT_TBL.articleID = KEY_TBL2.[KEY]
WHERE  FT_TBL.inactive=0
    AND (KEY_TBL.[KEY] IS NOT NULL OR KEY_TBL2.[KEY] IS NOT NULL)
ORDER BY RANK DESC
)

-- snip
----
-- /snip

Upvotes: 0

Michael Dunlap
Michael Dunlap

Reputation: 4310

Looks like you could change the INNER JOIN on each to a LEFT JOIN and that would get you what you want. Though I'll admit I'm not familiar with CONTAINSTABLE

Upvotes: 0

Related Questions