Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 23rd, 2003, 06:45 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excluding items with certain keywords

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
Reply With Quote
  #2 (permalink)  
Old July 23rd, 2003, 07:59 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old July 23rd, 2003, 08:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #4 (permalink)  
Old July 23rd, 2003, 08:16 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
  #5 (permalink)  
Old July 23rd, 2003, 10:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Keywords on .CSS ? AMB CSS Cascading Style Sheets 1 May 27th, 2006 09:15 PM
use of Not and IS Nothing keywords jay schumacher VB.NET 2002/2003 Basics 1 March 31st, 2006 10:01 PM
How to highlight 3 keywords in 3 colors? gilgalbiblewheel Classic ASP Databases 2 October 20th, 2004 06:01 PM
highlight search keywords allang JSP Basics 2 October 13th, 2004 01:09 AM
META KEYWORDS anshul HTML Code Clinic 6 September 11th, 2004 06:06 PM



All times are GMT -4. The time now is 03:56 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.