Excluding items with certain keywords

ashcarrot · July 23rd, 2003, 06:45 AM

I have a table of reports and assigned to each of them is a number of keywords

REPORT(id, description, etc)
KEYWORD(id, report_id, name)

i want to get reports where there aren't certain keywords

so something like

SELECT DISTINCT(report.id) from report,keyword WHERE report.id = keyword.id keyword NOT IN (1707,13,15)

But the trouble is with this as theres may be more than one keyword it may return a report where there is that keyword attached....

Thanks

12th_Man · July 23rd, 2003, 07:59 AM

SELECT REPORT.id
FROM REPORT
WHERE REPORT.id NOT IN
   (SELECT REPORT.id
    FROM REPORT, KEYWORD
    WHERE KEYWORD.report_id = REPORT.id
       AND KEYWORD.id IN (1707, 13, 15))

The sub query in the WHERE clause selects all the reports where one of the keywords is present, and the outer query selects all reports that do not exist in that group.

Jeff might give you a better/more efficient way to do it. I always defer to his methods, but this should work for now.

Jeff Mason · July 23rd, 2003, 08:11 AM

I think your solution is as good as it gets. That's exactly the one I was about to post when you beat me to it. :)

I have only one tangential observation. I like to use the SQL 92 standard syntax for specifying JOINs, rather than the "old style" method of specifying the JOIN condition in the WHERE clause:

Code:

...
    (SELECT REPORT.id
        FROM REPORT
            INNER JOIN KEYWORD ON KEYWORD.report_id = REPORT.id
        WHERE KEYWORD.id IN (1707, 13, 15))

It's easier on the optimizer :D, it avoids certain ambiguities which can arise when OUTER JOINS are involved, and it separates "legitimate" selection criteria from JOIN "overhead", thus making the WHERE clause easier for a person to read and understand. YMMV.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

12th_Man · July 23rd, 2003, 08:16 AM

I usually use the <table1> INNER JOIN <table2> ON <criteria> syntax, but recently I had to do some work on an Oracle DB (I used to use only MSSQL) and that syntax didn't seem to work. Have most RDBMS implemented the new syntax or is SQL Server one of the few?

Jeff Mason · July 23rd, 2003, 10:04 AM

Quote:

quote:Originally posted by 12th_Man
Have most RDBMS implemented the new syntax or is SQL Server one of the few?

No RDBMS supports the full SQL-92 specification (and there already is a SQL99 so everyone will fall even further behind). My guess is that there will be some obscure features of SQL92 that will never see the light of day in any implementation ever.

To the best of my knowledge, Oracle8 and up supports the standard SQL JOIN syntax. I'd venture to guess that any reasonably current SQL implementation will do so, but I do not have first hand knowledge of all of them... :)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com