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?