LEFT OUTER JOIN - revisited

I a previous question, I got a query that seemd to work perfect...

Now that I have tested it with a better dataset I can see that it does not :

The query is here :
SELECT TOP 1 a.docid,a.defname,a.status,a.blobdata FROM documents a LEFT JOIN web_timelock b ON a.docid = b.docid and (b.id = NULL OR (b.done=0 AND DATEDIFF (minute, b.ts, GETDATE())  > 5)) WHERE a.status = 4 AND ( a.defname = 'O_LEM' OR a.defname = 'N_LEM' OR a.defname = 'N_LEM_11' OR a.defname = 'O_LEM_11') ORDER BY a.createtime ASC;

The tables are:
dbo.documents
---------------------------------
docid :: <bigint>
status :: <int>
defname :: <varchar>
blobdata :: <binary data>

dbo.web_timelock
---------------------------------
id :: <bigint>
docid :: <bigid>
ts :: <datetime>
userid :: <int>
done :: <tinyint>

The problem is that it comes up with the same record even though web_timelock contains a record with the specific docid where done=1 (or ts is less that 5 minutes old)

Any takers ?
LVL 2
trg_dkAsked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
SELECT     TOP (1) a.docid, a.defname, a.status, a.blobdata
FROM         documents AS a LEFT OUTER JOIN
                    web_timelock AS b ON a.docid = b.docid
where b.id is NULL
0
 
momi_sabagCommented:
SELECT TOP 1 a.docid,a.defname,a.status,a.blobdata
FROM documents a
LEFT JOIN web_timelock b
ON a.docid = b.docid and (b.done=0 or DATEDIFF (minute, b.ts, GETDATE())  > 5)

WHERE a.status = 4 AND ( a.defname = 'O_LEM' OR a.defname = 'N_LEM' OR a.defname = 'N_LEM_11' OR a.defname = 'O_LEM_11')
ORDER BY a.createtime ASC;

0
 
trg_dkAuthor Commented:
>momi_sbaq - Hi again :-)

The query you propose does not work. It returns a row that has a related row in web_timelock where done=1.

What I need is to find a row in documents that does NOT have a corresponding row in web_timelock (OR that corresponding row has done==0 and ts older than 5 minutes)

Cheers,
Mark
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
momi_sabagCommented:
SELECT TOP 1 a.docid,a.defname,a.status,a.blobdata
FROM documents a
LEFT JOIN web_timelock b
ON a.docid = b.docid and b.done=0 and DATEDIFF (minute, b.ts, GETDATE())  > 5

WHERE a.status = 4 AND ( a.defname = 'O_LEM' OR a.defname = 'N_LEM' OR a.defname = 'N_LEM_11' OR a.defname = 'O_LEM_11')
ORDER BY a.createtime ASC;
0
 
trg_dkAuthor Commented:
Same result :-(
0
 
trg_dkAuthor Commented:
A simple version like this:
SELECT     TOP (1) a.docid, a.defname, a.status, a.blobdata
FROM         documents AS a LEFT OUTER JOIN
                      web_timelock AS b ON a.docid = b.docid AND b.id = NULL

Also fails - as I see it it should return a row from documents that DONT have a row in web_timelock ?!?!
0
 
momi_sabagCommented:
SELECT     TOP (1) a.docid, a.defname, a.status, a.blobdata
FROM         documents AS a LEFT OUTER JOIN
                     web_timelock AS b ON a.docid = b.docid
where b.id = NULL

0
 
momi_sabagCommented:
you need to understand how left outer join works and you will figure it out

left outer join works this way:
for every row from the left table (the one to the left of the join clause) :
   search for a matching row in the right table (according to the conditions in the ON clause)
     if found - add the row from the right table to the result
    else add  nulls to the result instead of the row from the right table

so filtering
where b.id = null
can't be done in the ON clause, only in the where clause
0
 
trg_dkAuthor Commented:
SELECT     TOP (1) a.docid, a.defname, a.status, a.blobdata
FROM         documents AS a LEFT OUTER JOIN
                     web_timelock AS b ON a.docid = b.docid
where b.id = NULL

Returns 0 records ?? (and there should be at least 20.000)
0
 
trg_dkAuthor Commented:
:-O

The problem was b.id = NULL (wrong) instead on b.id IS NULL

Thanks for your help :-) (again)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.