Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

LEFT OUTER JOIN - revisited

Posted on 2008-11-20
10
Medium Priority
?
159 Views
Last Modified: 2012-05-05
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 ?
0
Comment
Question by:trg_dk
  • 5
  • 5
10 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 23003686
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
 
LVL 2

Author Comment

by:trg_dk
ID: 23003750
>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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 23003781
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 2

Author Comment

by:trg_dk
ID: 23003815
Same result :-(
0
 
LVL 2

Author Comment

by:trg_dk
ID: 23003837
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 23003851
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 23003868
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
 
LVL 2

Author Comment

by:trg_dk
ID: 23003873
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
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 23003973
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
 
LVL 2

Author Comment

by:trg_dk
ID: 23004241
:-O

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

Thanks for your help :-) (again)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Loops Section Overview

810 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