Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Exception Join in SQL Server

Posted on 2006-05-23
6
Medium Priority
?
6,763 Views
Last Modified: 2008-02-07
Is there a more efficient way to perform an exception join on two (very large) tables than using the where clause below?

I want to get just the docnumbers that don't exist in the transactionlog with the criteria in the nested where clause.

select dt.documenttype as 'Document Type'
from docdata itd
join doctypes dt
on itd.doctypenum = dt.doctypenum
and dt.filetypenum = 1
where itd.docnum not in
(select docnum from transactionlog where action=123 and transactiondate > '2005-05-01')

Thanks.
0
Comment
Question by:skimmel
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1000 total points
ID: 16744312
yes use EXISTS

select dt.documenttype as 'Document Type'
from docdata itd
join doctypes dt
on itd.doctypenum = dt.doctypenum
and dt.filetypenum = 1
where NOT EXISTS  
(select docnum
    from  transactionlog as x  
  where action=123 and transactiondate > '2005-05-01'
     and itd.Docnum = x.docnum
)

0
 

Author Comment

by:skimmel
ID: 16745618
Exists seemed to work better, but I found a flaw in my logic from that.  My corrected original query is below (and running slower than before).  Here is the table structure too.

doctypes contains information about the document types
Columns: (documenttype, doctypenum, and filetypenum; analagous to 'Year End Report', '001' (Year End Report's ID), and '01' (Text Report))

docdata contains information about each individual document in the system
Columns: (doctypenum (same as doctypes table), itemnum (unique document id))

transactionlog contains information about who created, accessed, or modified a document
Columns: (itemnum (same as docdata), action (integer describing whether it was created, accessed, or modified), and transactiondate (when the action occured))

select dt.documenttype as 'Document Type'
from docdata itd
join doctypes dt
on itd.doctypenum = dt.doctypenum
and dt.filetypenum = 1
where itd.doctypenum not in
(select doctypenum
from docdata witd
join transactionlog wtx
on wtx.itemnum = witd.itemnum
where wtx.action=123
and wtx.transactiondate > '2005-05-01')

I need to implement a distinct in here somewhere too, since I can have many documents in each doctype.  This query needs to tell me any document types that did not have any documents accessed during the time frame specified.

Thanks.
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 16749917
Going back to Lowfatspread's suggestion of using EXISTS, how about something like the following?

select dt.documenttype as 'Document Type'
from docdata itd
join doctypes dt
on itd.doctypenum = dt.doctypenum
and dt.filetypenum = 1
where NOT EXISTS
(select TOP 1 doctypenum
from docdata witd
join transactionlog wtx
on wtx.itemnum = witd.itemnum
where
witd.doctypenum = itd.doctypenum and
wtx.action=123 and
wtx.transactiondate > '2005-05-01')


 FWIW, I'd probably try something like:

select dt.documenttype as 'Document Type'
from docdata itd

join doctypes dt
on itd.doctypenum = dt.doctypenum
and dt.filetypenum = 1

left outer join transactionlog wtx
on wtx.itemnum = itd.itemnum and
wtx.action=123 and
wtx.transactiondate > '2005-05-01'

where
wtx.itemnum IS NULL


 James
0
 
LVL 35

Expert Comment

by:James0628
ID: 16749935
As for the distinct requirement you mentioned, maybe just add a DISTINCT to the first SELECT?

 James
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question