Schwieriger JOIN

Hallo!

Ich habe das Problem, dass ich nicht weiß, wie ich die folgende Abfrage
formulieren kann.

Gegeben sind zwei beispielhafte Tabellen. Die eine ist eine mit
"Ressourcen", die andere ist praktisch ein Zugriffs-Log, in dem
gespeichert wird, welcher Nutzer auf welche Ressource zugegriffen hat.

1) resources:
+--------------+------+
| resource_id | info |
+--------------+------+
| 1 | ... |
| 2 | ... |
| 3 | ... |
+--------------+------+

2) resource_access
+--------------+---------+
| resource_id | user_id |
+--------------+---------+
| 1 | 1 |
| 3 | 2 |
| 1 | 2 |
+--------------+---------+

Was ich will: Alle Ressourcen auflisten, auf die ein bestimmter Nutzer
noch *nicht* zugegriffen hat.

Sprich: Am liebsten mit einem einzigen Statement alle resource_ids
ausgeben, die der Nutzer mit einer bestimmten user_id noch nicht gesehen
hat.

Relativ einfach ist der Fall, wenn ich die Ressourcen anzeigen will, die
noch von keinem Nutzer abgerufen wurden:

SELECT resource_id
FROM resources r
LEFT JOIN resource_access ra ON r.resource_id=ra.resource_id
WHERE user_id IS NULL

Aber wie bekomme ich z.B. alle resource_id, die der Nutzer mit der
user_id=1 noch nicht gesehen hat?

Erschwerend kommt hinzu, dass die MySQL-Version 4.0.27 ist und ich auf
dem Server nicht das Recht habe, temporäre Tabellen anzulegen.

Danke im Voraus!

Marian

Re: Schwieriger JOIN

Marian Steinbach wrote:
> Gegeben sind zwei beispielhafte Tabellen. Die eine ist eine mit
> "Ressourcen", die andere ist praktisch ein Zugriffs-Log, in dem
> gespeichert wird, welcher Nutzer auf welche Ressource zugegriffen hat.
>
> 1) resources:
> +--------------+------+
> | resource_id | info |
> +--------------+------+
[snip]
> 2) resource_access
> +--------------+---------+
> | resource_id | user_id |
> +--------------+---------+
[snip]
> Was ich will: Alle Ressourcen auflisten, auf die ein bestimmter Nutzer
> noch *nicht* zugegriffen hat.
>
> Sprich: Am liebsten mit einem einzigen Statement alle resource_ids
> ausgeben, die der Nutzer mit einer bestimmten user_id noch nicht gesehen
> hat.

Wenn man die Problembeschreibung umformuliert sollte es klar werden:

Du möchtest von allen möglichen Benutzer-Ressource-Kombinationen wissen,
ob sie in resource_access enthalten sind und nur die ausgeben, die es
nicht sind.

Also bildest du erst das vollständige Kreuzprodukt zwischen Benutzern
und Ressourcen und LEFT JOINst dann resource_access mit den beiden
Bedingungen.

SELECT *
FROM resources r
INNER JOIN user u
LEFT JOIN resource_access ra
ON ra.resource_id=r.resource_id
AND ra.user_id=u.user_id
WHERE ra.resource_id IS NULL;

(ungetestet)

> Erschwerend kommt hinzu, dass die MySQL-Version 4.0.27 ist und ich auf
> dem Server nicht das Recht habe, temporäre Tabellen anzulegen.

Die braucht man ohnehin nicht.

Grüße
Kai

--
This signature is left as an exercise for the reader.

Re: Schwieriger JOIN

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Schwieriger JOIN

Kai Ruhnau schrieb:
> ...
>
> Wenn man die Problembeschreibung umformuliert sollte es klar werden:
>
> Du möchtest von allen möglichen Benutzer-Ressource-Kombinationen wissen,
> ob sie in resource_access enthalten sind und nur die ausgeben, die es
> nicht sind.
>
> Also bildest du erst das vollständige Kreuzprodukt zwischen Benutzern
> und Ressourcen und LEFT JOINst dann resource_access mit den beiden
> Bedingungen.
>
> SELECT *
> FROM resources r
> INNER JOIN user u
> LEFT JOIN resource_access ra
> ON ra.resource_id=r.resource_id
> AND ra.user_id=u.user_id
> WHERE ra.resource_id IS NULL;

Ich weiß nicht recht...

Ich will eigentlich nicht "allen möglichen
Benutzer-Ressource-Kombinationen" wissen... ich will eigentlich nur die
meines gerade interessanten Nutzers (bspw. user_id=1) wissen. Wie bringe
ich das in dem Statement unter?

Danke für die Hilfe!

Marian

Re: Schwieriger JOIN

Marian Steinbach wrote:

> SELECT resource_id
> FROM resources r
> LEFT JOIN resource_access ra ON r.resource_id=ra.resource_id
> WHERE user_id IS NULL
>
> Aber wie bekomme ich z.B. alle resource_id, die der Nutzer mit der
> user_id=1 noch nicht gesehen hat?


SELECT resource_id
FROM resources r
LEFT JOIN resource_access ra ON r.resource_id=ra.resource_id
AND user_id=1
WHERE user_id IS NULL

Dieter

Re: Schwieriger JOIN

Marian Steinbach wrote:

>Kai Ruhnau schrieb:
>> Also bildest du erst das vollständige Kreuzprodukt zwischen Benutzern
>> und Ressourcen und LEFT JOINst dann resource_access mit den beiden
>> Bedingungen.
>>
>> SELECT *
>> FROM resources r
>> INNER JOIN user u
>> LEFT JOIN resource_access ra
>> ON ra.resource_id=r.resource_id
>> AND ra.user_id=u.user_id
>> WHERE ra.resource_id IS NULL;
>
> Ich weiß nicht recht...
>
> Ich will eigentlich nicht "allen möglichen
> Benutzer-Ressource-Kombinationen" wissen... ich will eigentlich nur die
> meines gerade interessanten Nutzers (bspw. user_id=1) wissen. Wie
> bringe ich das in dem Statement unter?

Indem Du das als zusätzliche Bedingung angibst - wenn Du alle bekommst
und willst sie einschränken, hilft generell eine zusätzliche Bedingung
im WHERE.


Nun ist mit dieser Einschränkung aber auch die Tabelle user unnötig ->

SELECT *
FROM resources r
LEFT JOIN resource_access ra
ON ra.resource_id=r.resource_id
AND ra.user_id=1
WHERE ra.resource_id IS NULL;

sollte es einklich tun.


Thomas

Re: Schwieriger JOIN

Dieter Noeth schrieb:
> Marian Steinbach wrote:
>
>> SELECT resource_id
>> FROM resources r
>> LEFT JOIN resource_access ra ON r.resource_id=ra.resource_id
>> WHERE user_id IS NULL
>>
>> Aber wie bekomme ich z.B. alle resource_id, die der Nutzer mit der
>> user_id=1 noch nicht gesehen hat?
>
>
> SELECT resource_id
> FROM resources r
> LEFT JOIN resource_access ra ON r.resource_id=ra.resource_id
> AND user_id=1
> WHERE user_id IS NULL
>
> Dieter

Danke, das hat letztlich geholfen! Ich wusste bisher nicht, dass ich
dafür im "ON"-Statement weitere Bedingungen unterbringen kann.

Danke auch den anderen!

Marian