[MYSQL] Mit einer Abfrage mehrere Tabellen durchsuchen

[glowhand]

Hallo,

wieder mal eine Frage von mir
Ich möchte für einen User 50 Banner aus einer Datenbank auslesen lassen. Diese Banner sind alle in einer Tabelle gespeichert... gleichzeitig soll aber getestet werden, ob der jeweilige Banner sich bereits in der Reloadsperre befindet... Die Reloadsperre-Einträge sind in einer anderen Tabelle gespeichert...
Wichtig ist, dass eben, wenn 50 Banner (die sich nicht in der Reloadsperre befinden) vorhanden, diese auch alle ausgelesen werden.
Das ganze könnte man natürlich über mehrere Abfragen regeln... aber ich glaube, mit einer geht es schneller...
Nur wie praktiziere ich das? Ich glaube, dass es mit dem Schlüsselwort JOIN zusammenhängt... nur habe ich bisher noch kein einfaches Tutorial dazu gefunden.
Vielleicht kann mir ja mal jemand einen Beispielcode posten.
Vielen Dank.

 

[ice-breaker]

mittels einem Left-Join:
https://www.klamm.de/forum/showthread.php?t=2198

oder einem subselect:

SELECT foo FROM bar WHERE id NOT IN(SELECT id FROM blacklist WHERE foo=bar)

 

[glowhand]

dank dir erstmal...
da smit dem subselect gefällt mir ja nicht so, und das mit dem left join versteh ich nicht... da kann ich mir den Code ansehen, aber für was der nun sorgt ist mir ein rätsel...
kennst du vielleicht eine einfache Erklärung für diese JOIN-Befehle?
Wie gesagt, alles, was ich bisher gefunden habe, war ziemlich unverständlich.
Bin anscheinend ein wenig schwer von Begriff

 

[ice-breaker]

da smit dem subselect gefällt mir ja nicht so

ich finde ihn klasse, schön simpel^^ nur leider von MySQL noch nicht so dolle optimiert

und das mit dem left join versteh ich nicht... da kann ich mir den Code ansehen, aber für was der nun sorgt ist mir ein rätsel...
kennst du vielleicht eine einfache Erklärung für diese JOIN-Befehle?
Wie gesagt, alles, was ich bisher gefunden habe, war ziemlich unverständlich.
Bin anscheinend ein wenig schwer von Begriff

kann dir auch nur das Manual anbieten:
https://dev.mysql.com/doc/refman/5.1/en/join.html

 

[flaschenkind]

SELECT `banner`.`id` FROM `banner` LEFT JOIN `reloadsperre` ON (`banner`.`id` != `reloadsperre`.`banner_id`)

Ungetestet und grad zu müde, um mir viele Gedanken zu machten. Sollte aber funktionieren

 

[glowhand]

also ich habe es jetzt erst einmal mit einem subselect gelöst, nochmals danke an icq-breaker...
flaschenkind, deinen code habe ich auch mal ausprobiert, aber irgendwie liest er die banner IMMER aus, auch wenn sie in der reloadsperre sind.
außerdem müsste da ja noch zusätzlich nach dem mitgliedsnamen gefragt werden... und wobei ich mir nicht so sicher bin: kontrolliert JOIN jede einzelne id der banner mit jeder einzelnen eingetragenen id der reloadsperre?

 

[ZeroCCC]

also ich habe es jetzt erst einmal mit einem subselect gelöst, nochmals danke an icq-breaker...

Wenn du was mit nem JOIN lösen kann, dann mach das auch Die passende Lösung wurde auch schon gepostet, das problem trit hier jeden Monat auf Subselects sind arschlahm (bei Mysql), da bewegt man sich sehr schnell im Sekunden bereich mit Abfragen.

 

[ice-breaker]

Wenn du was mit nem JOIN lösen kann, dann mach das auch Die passende Lösung wurde auch schon gepostet, das problem trit hier jeden Monat auf Subselects sind arschlahm (bei Mysql), da bewegt man sich sehr schnell im Sekunden bereich mit Abfragen.

das ist eine pauschale aussage, sub-selects sind definitiv NICHT arschlahm, sonst wären sie ja auch noch immer nicht final in MySQL drinne, das einzige problem was noch mit sub-selects besteht, ist dass diese noch nicht immer wirklich dolle von dem MySQL Query Optimizer optimiert werden, weshalb es mal passieren kann, dass er in einem Sub-Select keine Indizes nutzt, aber sonst gibt es da keine Probleme.

 

[ZeroCCC]

das ist eine pauschale aussage, sub-selects sind definitiv NICHT arschlahm, sonst wären sie ja auch noch immer nicht final in MySQL drinne, das einzige problem was noch mit sub-selects besteht, ist dass diese noch nicht immer wirklich dolle von dem MySQL Query Optimizer optimiert werden, weshalb es mal passieren kann, dass er in einem Sub-Select keine Indizes nutzt, aber sonst gibt es da keine Probleme.

Immer wenn ich sowas wie "SELECT * FROM blub WHERE blub.id IN (SELECT...)" benutze, hab ichs gefühl ich komm mit selbst raussuchen schneller. Und auch solche Sachen wie "SELECT * FROM (SELECT * FROM blub) AS a" gehen auch nicht unbedingt schnell, liegen aber noch im vertretbaren Bereich.

 

[glowhand]

ich würde ja gerne JOIN versuchen, aber es funktioniert einfach nicht...
hab folgenden code geschrieben, der, selbst wenn er funktionieren würde, die einzelnen user nicht beachtet (also ist ein banner in de reloadsperre eines users, wird er ja für alle user geblockt):

# Banner, die nicht in der Reloadsperre des Mitglieds liegen, auslesen
$result = $db->query("SELECT banner.id,banner.tan,banner.titel,banner.bannerurl,banner.url,banner.verweildauer,banner.verguetung,banner.verguetungsart,banner.reloadsperre,banner.gebucht,banner.vorhanden
					  FROM ".MYSQL_PREFIX."_banner AS banner
					  LEFT JOIN ".MYSQL_PREFIX."_banner_reload AS banner_reload
					  ON (banner.id != banner_reload.bannerID)");

 

[Johnson]

Sollte in etwa so gehen:

SELECT * FROM banner AS B
LEFT JOIN banner_reload AS BR ON BR.id = B.id AND BR.id IS NULL
WHERE B.owner = USERID

 

[glowhand]

Sollte in etwa so gehen:

SELECT * FROM banner AS B
LEFT JOIN banner_reload AS BR ON BR.id = B.id AND BR.id IS NULL
WHERE B.owner = USERID

funzt auch nicht, aber ich denke, ich habe das prinzip verstanden... was mich mich aber nicht zur lösung bringt... also dein code müsste meines erachtens nach folgendermaßen augesprichen lauten:

lies ALLES aus banner UND verbinde die einzelnen einträge mit denen von banner_reload, BEI DEM der jeweilige eintrag von banner die gleiche id hat wie der von banner_reload, aber gleichzeitig soll die id von banner_reload nicht vorhanden sein... nd das dort wo die mitgliedsid XX ist...
er verbindet also garnicht mit banner_reload, weil die ID einerseits die gleiche sein, aber gleichzeitig auch nicht vorhanden sein soll.



es muss also eine bedingung geben, die vorschreibt:
Lies ALLE einträge aus banner, deren ID entweder in banner_reload NICHT existiert oder EXISTIERT, aber der mitgliedsname nicht der vom jeweiligen mitglied ist..
aber wie formuliere ich das um

 

[Thomas]

sei time eine spalte (not null) die nur in der tabelle reload_sperre auftaucht
dann gibt dir die folgende querry alle banner aus, die keinen eintrag in der reload_sperre haben oder alternativ, die reloadzeit abgelaufen ist

SELECT alles was du brauchst
FROM banner b
LEFT JOIN reload_sperre r ON r.banner_id=b.banner_id
WHERE r.time is null
OR r.time ist abgelaufen

vielleicht hilft dir das ja weiter ;-)

 

[glowhand]

nee auch nicht
die reloadzeit ist uninteressant. einträge, deren reloadzeit abgelaufen sind, sind bereits gelöscht worden .
es muss aber noch der mitgliedsname abgefragt werden.

 

[Thomas]

na dann nimmst du das or weg und packst statt dessen ein "and user=sowieso" dran

 

[glowhand]

heißt also:

lies ALLE einträge aus banner aus und verbinde die einträge von banner_reload, deren jeweilige banner-id die gleiche ist wie die der jeweiligen id von banner, bei der der mitgliedsname gleich xxx ist.

das heißt, er gibt wieder ALLE banner aus... und getestet tut er das auch...

# Banner, die nicht in der Reloadsperre des Mitglieds liegen, auslesen
$result = $db->query("SELECT B.id,B.tan,B.titel,B.bannerurl,B.url,B.verweildauer,B.verguetung,B.verguetungsart,B.reloadsperre,B.gebucht,B.vorhanden
                      FROM ".MYSQL_PREFIX."_banner AS B
                      LEFT JOIN ".MYSQL_PREFIX."_banner_reload AS BR
                      ON B.id != BR.bannerID 
                      AND BR.mitgliedsname = '%s'",$_SESSION['mitgliedsname']);

ich glaube aber langsam, JOIN ist nicht die richtige Wahl für das Problem, da JOIN verbindet. Es soll aber nicht verbunden, sondern ausgeschlossen werden.

 

[Thomas]

ok, dann mal ausführlich...

bsp-schema:
banner(id,url)
reload(id, user, time)

bsp-inhalt:
banner:
1, blah.jpg
2, xyz.jpg
3, tzz.jpg

reload
2, thomas, 1234
2, alf, 1234
3, alf, 1234
2, max, 1234


bei einem normalen join über id stehen im ergebnis nur die ids, die in beiden tabellen vorkommen, also

SELECT * FROM banner b, reload r WHERE b.id=r.id
2, xyz.jpg, thomas, 1234
2, xyz.jpg, alf, 1234
3, tzz.jpg , alf, 1234
2, xyz.jpg, max, 1234

die 1 fällt raus, weil dazu kein eintrag in reload existiert



bei einem left join bleiben die einträge erhalten und alle werte die nicht existieren werden auf null gesetzt, also

SELECT * FROM banner b LEFT JOIN reload r ON b.id=r.id
1, blah.jpg, null, null
2, xyz.jpg , 1234, thomas
2, xyz.jpg , 1234, alf
3, tzz.jpg , 1234, alf
2, xyz.jpg, max, 1234

Und du möchtest nun alle die banner für einen user, die nicht unter seinem namen in der reload sperre sind, also zum beispiel meinen...

SELECT *
FROM banner b
LEFT JOIN reload r ON b.id=r.id
WHERE user<>thomas

1, blah.jpg, null, null
2, xyz.jpg , 1234, alf
3, tzz.jpg , 1234, alf
2, xyz.jpg, max, 1234

selektierst dann nur noch das was du brauchst

SELECT id, url
FROM banner b
LEFT JOIN reload r ON b.id=r.id
WHERE user<>thomas

1, blah.jpg
2, xyz.jpg
3, tzz.jpg
2, xyz.jpg

und musst dann noch duplikate eliminieren



unter der annahme, dass es aber nur 50 banner sind, dafür aber wesentlich mehr einträge in der reloadsperre würde ich es auch wie ice-breaker machen

SELECT id, url FROM banner WHERE id not in (SELECT id FROM reload WHERE user<>'thomas')



edit: ach ja, wenn man einen lahmen query-optimizer für subqueries befürchtet und mit left joins nicht zu rande kommt, kann man es ja auch so machen:
SELECT id FROM reload WHERE user='thomas'

die ids in php umformen in id_1,id_2,id_3,...
und dann die banner selektieren
SELECT id, url FROM banner WHERE id not in (id_1,id_2,id_3,...)

 

[glowhand]

ah danke, also is nen subselect doch besser
dank dir

 

[Scar]

habt ihr aus reinem interesse mal die zeiten gemessen, ob GROUP BY und hier dann wohl auch LIMIT wirklich langsamer ist, als subquerys bzw. (noch schlimmer) alles einzulesen und anschließend erneut zu senden (also zwei eigenständige querys)?

besonders der fall von zwei querys mag noch teilweise für systeme irrelevant sein, bei denn web- und dbserver auf dem selben rechner laufen. in allen anderen fällen sehe ich hier aber ein kritisches nadelöhr.