Zu viele Ergebnisse

[S2B]

Hi @all,
ich habe ein kleines Problemchen mit einem Query:

SQL-Code:

SELECT count(*) FROM phpbb_voc_cat c, phpbb_voc_rows r
LEFT JOIN phpbb_voc v ON v.cat_id = c.cat_id WHERE r.cat_id = c.cat_id AND c.cat_id = 2
ORDER BY r.row_id ASC, v.voc_id DESC

Ich möchte gerne, dass ich nachher 8 Ergebnisse zurückgeliefert bekomme, so viele Einträge sind nämlich in der phpbb_voc-Tabelle. Nur leider erhalte ich mit diesem Query 200 Ergebnisse, nämlich alles doppelt und dreifach.
Ich hoffe, ihr könnt mir helfen.

PS: Die Tabellen sehen so aus:

SQL-Code:

CREATE TABLE `phpbb_voc` (
  `voc_id` mediumint(8) NOT NULL default '0',
  `cat_id` mediumint(8) NOT NULL default '0',
  `voc_row` smallint(2) NOT NULL default '0',
  `voc_text` text NOT NULL,
  KEY `voc_id` (`voc_id`)
) TYPE=MyISAM;

CREATE TABLE `phpbb_voc_cat` (
  `cat_id` mediumint(8) NOT NULL default '0',
  `cat_name` varchar(80) NOT NULL default '',
  `cat_desc` text NOT NULL,
  `cat_mod` mediumint(8) NOT NULL default '0',
  `cat_auth_view` smallint(1) NOT NULL default '0',
  `cat_auth_write` smallint(1) NOT NULL default '1',
  PRIMARY KEY  (`cat_id`)
) TYPE=MyISAM;

CREATE TABLE `phpbb_voc_rows` (
  `row_id` mediumint(2) NOT NULL default '0',
  `cat_id` mediumint(8) NOT NULL default '0',
  `row_width` varchar(4) NOT NULL default '0',
  `row_title` varchar(80) NOT NULL default '',
  KEY `row_id` (`row_id`)
) TYPE=MyISAM;

[alzaimar]

Ich habe mir die Tabellen im Einzelnen *nicht* angesehen, aber ein LEFT JOIN liefert alle Ergebnisse der 'linken' Seite, egal, ob die mit der 'rechten' Seite in Einklang (joined) gebracht werden können.

Das Konstruct "select * from a,b where a.bla = b.bla" ist alt und ungenau. Es sollte nicht mehr verwendet werden. Benutze lieber reine joins. hier also "select * from a join b on a.bla = b.bla"

Wenn Du zählst und mehrere Tabellen 'joinst', dann befolge folgende Regeln:
1. WAS willst Du zählen? Count (*) ist manchmal ungenau. Vielleicht willst Du die ID's nur zählen. Benutze DISTINCT, um doppelte Treffer zu eliminieren

2. Verwende "Left join" nur dann, wenn Du wirklich ALLE Zeilen der linken Tabelle haben willst. Das ist bei einem Aggregat (hier: Count) i.a. nicht erwünscht.

3. Erstelle erstmal die Ergebnismenge und überprüfe die genau. Erst wenn Du sicher bist, das Richtige zu bekommen, ersetze das * durch das Aggregat.

4. Ein Trick: Manchmal ist es einfacher, SUM() anstatt COUNT() zu verwenden. Wenn Du z.B. nur bestimmte Kandidaten zählen willst, oder gleich mehrere Zählungen auf einmal durchführen willst, dann mach es so: Sei "Friends" eine Tabelle meiner Kumpels und "Age" das Alter. Dieses Select liefert mir alle Rentner, sowie gleichzeitig noch die über 40 Jährigen...

Delphi-Quellcode:

select sum (case when age >=65 then 1 else 0 end) as Rentner,
sum (case when age between 40 and 65 then 1 else 0 end) as AlteSaecke
from Friends

Abschliessend ein Tip: Ich stelle mir ein Join immer als zwei sich überschneidende Kreise vor, wie die gute alte Mengenlehre in der Grundschule.
Join liefert die 'Mitte'.
Left Join liefert den linken Kreis (und alles, was in der Mitte ist).
Right Join liefert den rechten Kreis (und alles, was in der Mitte ist).
Full Join liefert Alles.

So genug geklugscheissert.

[Bernhard Geyer]

Zitat von S2B:

Ich möchte gerne, dass ich nachher 8 Ergebnisse zurückgeliefert bekomme...

Dafür gibt es nocht die LIMIT-Option im SELECT-Befehl bei MySQL.
Sonst hat alles weitere alzaimar erklärt.

[alzaimar]

Ein select (*) from bla liefert ja immer nur genau eine Zeile, da bringt das 'LIMIT' nichts

Und wenn er 8 zurückhaben will, versuchs doch mal mit 'select 8'

[S2B]

@alzaimar:
So, ich hab mich gerade mal durch deinen Beitrag durchgearbeitet.

1. Das COUNT(*) war in dem Fall nur zum Überprüfen des Querys gedacht und kommt im richtigen Query nicht vor.
2. Wenn ich mir das mit den 2 Kreisen vorstelle, bzw. in dem Fall wohl 3, dann trifft keine der 3 Möglichkeiten (LEFT JOIN, RIGHT JOIN, FULL JOIN) auf meinen Fall zu, weil ich ja die Schnittmenge brauche, und die nur einmal. Es soll also kein Wert doppelt vorkommen.
3. SUM fällt logischerweise auch raus, weil COUNT(*) nur ein Beispiel war.

Hast du jetzt auch noch einen konkreten Lösungsvorschlag für mein Problem, also wie ich die Schnittmenge der 3 Ergebnismengen erhalte?

@Bernhard:
Das ist ja nicht das Problem, ich kenne die LIMIT-Option schon, aber ich erhalte ja manche Einträge doppelt zurück. Ich möchte nicht bei jedem Mal nur 30 Ergebnisse (z.B.) erhalten, sondern jeden Eintrag in phpbb_voc nur einmal.

[alzaimar]

Acho so: Die Schnittmenge geht mit:
select * from c join r on c.cID = r.cID join v on v.cID = r.cID
where c.cID = 2

Fertig. Das mit den Kreisen greift hier auch:
Erstes Join (c mit r): 2 Kreise, c und r : Join = Schnittmenge S
Zweites Join (S mit v: 2 Kreise, S und v : Join = Schnittmenge X
X ist dann dein gewünschtes Ergebnis.

Laut Deinem Text willst Du aber Alle 'v' auflisten. Dann wäre das (ich ziehe die Tabelle, die mich interessiert, immer nach vorne):
select * from v left join (c join r on c.cID = r.cID) on r.cID = v.cID
where c.cID = 2

Es geht auch ohne Klammern. dann mit
select * from v left join r on r.cID = v.cID join r on c.cID = r.cID where c.cID = 2

Der Trick mit den Kreisen funktioniert deswegen, weil der Parser die Joins von links nach rechts durcharbeitet. Ob er dabei die Reihenfolge aus Optimierungsgründen vertauscht, braucht uns nicht zu interessieren.

"If in doubt, use braces Or pray."

[S2B]

Das bringt mir alles gar nichts, was du da geschrieben hast. Beim ersten Query erhalte ich wieder als Ergebnis 200 Datensätze und beim zweiten sind es schon 750. Evtl. hast du mich auch falsch verstanden:
Ich möchte solche Results haben:

Zitat:

cat_id | cat_name | cat_desc | cat_mod | cat_auth_view | cat_auth_write | row_id | cat_id | row_width | row_title | voc_id | cat_id | voc_row | voc_text

Die fettgedruckten sollen immer gleich sein. Bis jetzt erhalte ich immer solche Ergebnisse:

Zitat:

2 | name | beschreibung | 2 | 1 | 1 | 1 | 2 | 50% | titel | 2 | 2 | 1 | text
2 | name | beschreibung | 2 | 1 | 1 | 2 | 2 | 50% | titel | 2 | 2 | 2 | text
2 | name | beschreibung | 2 | 1 | 1 | 1 | 2 | 50% | titel | 1 | 2 | 1 | text
2 | name | beschreibung | 2 | 1 | 1 | 2 | 2 | 50% | titel | 1 | 2 | 2 | text
2 | name | beschreibung | 2 | 1 | 1 | 1 | 2 | 50% | titel | 2 | 2 | 1 | text
2 | name | beschreibung | 2 | 1 | 1 | 2 | 2 | 50% | titel | 2 | 2 | 2 | text
2 | name | beschreibung | 2 | 1 | 1 | 1 | 2 | 50% | titel | 1 | 2 | 1 | text
2 | name | beschreibung | 2 | 1 | 1 | 2 | 2 | 50% | titel | 1 | 2 | 2 | text

Das ist jetzt ein Beispiel, in dem es um eine cat_id, 2 row_id's und 2 voc_id's geht. Wie man (wahrscheinlich nicht *g*) erkennen kann, kommen die Ergebnisse mehrfach vor. Zuerst kommen alle Ergebnisse (in dem Fall 4), dann kommen diese aber noch einmal. Wenn ich jetzt 3 Vokabeln einfügen würde, wären es wohl 3 Male usw.
Ich möchte jetzt mit einem Query nur die ersten 4 Ergebnisse erhalten, da die anderen ja quasi sinnlos sind.

PS: Verwende doch bitte auch mal die SQL-Tags, dann kann man das besser entziffern.

[alzaimar]

Wenn Du 200 Einträge bei meinem ersten select bekommst, dann ist die Schnittmenge eben 200 Elemente gross.

select distinct ..... Dann siehst du nur die unterschiedlichen Zeilen.

Was ich aber nicht verstehe, ist, das Du die Zeilen mehrfach bekommst. So, wie ich das sehe, kann man dein Beispiel so eindampfen, das Tabellen mit jeweils einer Spalte (cat_id) übrig bleiben. Welche Werte stehen in v,c und r mit cat_id=2?

SQL-Code:

select count (*) from c where c.cID=2
select count (*) from r where r.cID=2
select count (*) from v where v.cID=2
select count (*) from c join r on c.cID = r.cID where c.cID=2

?

Und was ich auch immer noch nicht weiss, ist, *WAS* Du überhaupt angezeigt haben willst?
Ich habe hier mal drei Tabellen erstellt mit (nur die ID-Felder) Inhalten (1,2,3), (2,3) und (2,4). Die selects, die ich Dir geschickt habe, funktionieren (logischerweise).

[S2B]

So, nach längerem Suchen habe ich jetzt endlich die Lösung zu meinem Problem gefunden:

SQL-Code:

SELECT * FROM phpbb_voc v LEFT JOIN (phpbb_voc_cat c JOIN phpbb_voc_rows r ON c.cat_id = r.cat_id) 
ON r.cat_id = v.cat_id WHERE v.cat_id = c.cat_id AND r.row_id = v.voc_row AND c.cat_id = 2

Das Problem war, dass ich die row_id aus der phpbb_voc_rows nicht mit der voc_row aus der phpbb_voc gleichgesetzt hatte.
Vielen Dank für die Hilfe.

[alzaimar]

SQL-Code:

SELECT * FROM phpbb_voc v LEFT JOIN (phpbb_voc_cat c JOIN phpbb_voc_rows r ON c.cat_id = r.cat_id)
ON r.cat_id = v.cat_id WHERE v.cat_id = c.cat_id AND r.row_id = v.voc_row AND c.cat_id = 2

Kann es sein, das das "v.cat_id = c.cat_id" hinter dem "WHERE" überflüssig ist?

SQL-Code:

SELECT * FROM phpbb_voc v LEFT JOIN (phpbb_voc_cat c JOIN phpbb_voc_rows r ON c.cat_id = r.cat_id)
ON r.cat_id = v.cat_id WHERE r.row_id = v.voc_row AND c.cat_id = 2

Aber, egal, Hauptsache, Du hast die Lösung