SQLTeam.com | Weblogs | Forums

WHERE-Clause in JOIN


#1

Hi folks,

i got a problem concerning a conditional-clause in a JOIN. It would be great if someone could help me with that.
I got a table

CREATE TABLE beziehungen ( 
id int not null,
vater int,
benutzer varchar (30) not null;

in which relations between objects are stored in the attributes "id" and "vater". "Benutzer" is for storing who added an entry. Using a JOIN i read which entries got an id that is not used as vater itself. In short terms: which entry does not have child-entries. I do it using this statement:

SELECT 
a.id, a.vater, 
a.benutzer, a.rechner
FROM beziehungen AS a LEFT JOIN beziehungen AS b ON a.id = b.vater
WHERE b.vater Is Null;

It works fine so far, but the problem is, when the table is being populated with data by many users, who write identical entries, the it is possible, that an element is already added in the context of another user and because of that the statement return a wrong result.
My idea of solving this problem was to use a WHERE-Clause

SELECT 
a.id, a.vater, 
a.benutzer, a.rechner
FROM beziehungen AS a LEFT JOIN beziehungen AS b ON a.id = b.vater
WHERE b.vater Is Null AND a.benutzer='meinNutzer';

unfortunately this isn't working either, because no elements in my user context will be found that allready have children in another users context.

Does anyone have an idea how to query in a correct way in my user-context?

greetings
rodgerwilco


#2

Try this

SELECT 
a.id, a.vater, 
a.benutzer, a.rechner
FROM beziehungen AS a LEFT JOIN beziehungen AS b ON a.id = b.vater
AND a.benutzer='meinNutzer'
WHERE b.vater Is Null ;

#3

Maybe this ... the NOLOCK will allow you to read in-process INSERTs by other users, although it can also theoretically cause problems:

SELECT 
a.id, a.vater, 
a.benutzer, a.rechner
FROM beziehungen AS a 
WHERE
    NOT EXISTS(SELECT 1 FROM beziehungen AS b WITH (NOLOCK) WHERE a.id = b.vater)