LEFT JOIN verändert SUM-Ergebnis

[bubzilla]

Hallo zusammen,

hab folgende SQL-Query die Anmeldelisten auflistet von Seminaren und deren zugehörige Dozenten:

$sql = "
SELECT *, SUM(os.number) as waiting , SUM(os_p.number) as preview
FROM `seminar` AS sem
LEFT JOIN (`dozSemRef` AS dozRef, `dozent` AS doz) ON ( sem.`id` = dozRef.`seminarId` AND dozRef.`dozentId` = doz.`id`)
LEFT JOIN (`sem_locations` AS loc) ON ( sem.`location` = loc.`id` )
LEFT JOIN (order_seminar as os) ON (os.nr = sem.nr AND os.archiv = 3)
LEFT JOIN (order_seminar as os_p) ON (os_p.nr = sem.nr AND os_p.archiv = 6)
WHERE sem.release <= NOW()
AND end > NOW()
AND ( sem.archiv = 0 OR sem.archiv = 2 OR sem.archiv = 3 OR sem.archiv = 4 OR sem.archiv = 6 )
GROUP BY sem.id
ORDER BY ".$order." ".$direction." LIMIT ".(($index-1)*$elements).", ".$elements.";";

Das Problem ist das ich eine verdoppelung der SUM(os.number) as waiting und SUM(os_p.number) as preview bekomme. Irgenwie hat es damit zu tun wieviele Zeilen durch: LEFT JOIN (`dozSemRef` AS dozRef, `dozent` AS doz) ON ( sem.`id` = dozRef.`seminarId` AND dozRef.`dozentId` = doz.`id`) gefunden werden.

Hier nochmal der Kontext:

Ich suche je seminar alle "warteliste" (waiting) und "voranmeldung" (preview) Einträge und weitere Informationen zum seminar ( sem_locations - veranstaltungsort; dozent - alle dozenten)


Wie kann ich also die Anzahl der dozenten nicht auf meine SUM( ) ergebnisse wirken lassen?

DANKE

[Nightflyer]

Probier mal so

$sql = "
SELECT *, SUM(os.number) as waiting , SUM(os_p.number) as preview
FROM `seminar` AS sem
LEFT JOIN (`dozSemRef` AS dozRef) ON ( sem.`id` = dozRef.`seminarId` )
LEFT JOIN (`dozent` AS doz) ON ( dozRef.`dozentId` = doz.`id`)
LEFT JOIN (`sem_locations` AS loc) ON ( sem.`location` = loc.`id` )
LEFT JOIN (order_seminar as os) ON (os.nr = sem.nr AND os.archiv = 3)
LEFT JOIN (order_seminar as os_p) ON (os_p.nr = sem.nr AND os_p.archiv = 6)
WHERE sem.release <= NOW()
AND end > NOW()
AND sem.archiv IN(0,2,3,4,6) 
GROUP BY sem.id
ORDER BY ".$order." ".$direction." LIMIT ".(($index-1)*$elements).", ".$elements.";";

[bubzilla]

Super DANKE!

Sieht gut aus! Kannst du mir kurz erklären warum?

DANKE !

[bubzilla]

:-( nach nochmaligem Prüfen habe ich festgestellt das es doch nicht klappt!

Hatte nur einen dozenten zugeordnet!

Also immer noch falsch!

:-(

[Nightflyer]

Poste doch bitte mal einen Auszug aus deiner DB (Dump). Und was du genau willst

[bubzilla]

Danke.

Also hier die Felder:

CREATE TABLE `dozSemRef` (
  `id` int(11) NOT NULL auto_increment,
  `dozentId` int(11) NOT NULL,
  `seminarId` int(11) NOT NULL,
  `position` tinyint(4) NOT NULL,
  `modified` date NOT NULL,
  `archiv` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=32 ;

CREATE TABLE `dozent` (
  `id` int(11) NOT NULL auto_increment,
  `sex` varchar(10) collate latin1_german2_ci NOT NULL,
  `name` varchar(100) collate latin1_german2_ci NOT NULL,
  `forename` varchar(50) collate latin1_german2_ci NOT NULL,
  `subtitleLong` varchar(100) collate latin1_german2_ci NOT NULL,
  `subtitleShort` varchar(100) collate latin1_german2_ci NOT NULL,
  `description` mediumtext collate latin1_german2_ci NOT NULL,
  `photo` tinyint(4) NOT NULL,
  `modified` datetime NOT NULL,
  `archiv` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=36 ;

CREATE TABLE `order_seminar` (
  `id` int(11) NOT NULL auto_increment,
  `bill_number` varchar(20) collate latin1_german2_ci NOT NULL,
  `number` smallint(6) NOT NULL,
  `sessionId` varchar(50) collate latin1_german2_ci NOT NULL,
  `nr` varchar(20) collate latin1_german2_ci NOT NULL,
  `ref` varchar(20) collate latin1_german2_ci NOT NULL,
  `payed` mediumint(9) NOT NULL,
  `created` datetime NOT NULL,
  `archiv` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=11 ;


CREATE TABLE `sem_locations` (
  `id` int(11) NOT NULL auto_increment,
  `town` varchar(50) collate latin1_german2_ci NOT NULL,
  `zip` varchar(10) collate latin1_german2_ci NOT NULL,
  `street` varchar(50) collate latin1_german2_ci NOT NULL,
  `location` varchar(50) collate latin1_german2_ci NOT NULL,
  `directions` longtext collate latin1_german2_ci NOT NULL,
  `link` varchar(200) collate latin1_german2_ci NOT NULL,
  `link_name` varchar(50) collate latin1_german2_ci NOT NULL,
  `homepage` varchar(200) collate latin1_german2_ci NOT NULL,
  `homepage_name` varchar(50) collate latin1_german2_ci NOT NULL,
  `notize` mediumtext collate latin1_german2_ci NOT NULL,
  `modified` datetime NOT NULL,
  `archiv` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=35 ;


CREATE TABLE `seminar` (
  `id` int(11) NOT NULL auto_increment,
  `nr` varchar(20) collate latin1_german2_ci NOT NULL,
  `headline` varchar(100) collate latin1_german2_ci NOT NULL,
  `subtext` varchar(150) collate latin1_german2_ci NOT NULL,
  `mailinfo` mediumtext collate latin1_german2_ci NOT NULL,
  `pre_price_info` mediumtext collate latin1_german2_ci NOT NULL,
  `audience` varchar(200) collate latin1_german2_ci NOT NULL,
  `begin` datetime default NULL,
  `end` datetime default NULL,
  `date_kom` varchar(100) collate latin1_german2_ci NOT NULL,
  `date_default` varchar(50) collate latin1_german2_ci NOT NULL,
  `price` mediumint(9) NOT NULL,
  `pre_price` mediumint(9) NOT NULL,
  `price_kom` varchar(100) collate latin1_german2_ci NOT NULL,
  `location` int(11) NOT NULL,
  `location_kom` varchar(100) collate latin1_german2_ci NOT NULL,
  `members_max` mediumint(9) NOT NULL,
  `members_min` mediumint(9) NOT NULL,
  `booked` mediumint(9) NOT NULL,
  `reserve` tinyint(4) NOT NULL,
  `points` mediumint(9) NOT NULL,
  `description` mediumtext collate latin1_german2_ci NOT NULL,
  `topic` mediumtext collate latin1_german2_ci,
  `catRef` int(11) NOT NULL,
  `release` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `archiv` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=9 ;

ich möchte alle Seminare (tabelle: 'seminare') auflisten, einen dozenten ('dozent' über die referenz-tabelle 'dozSemRef') und deren veranstaltungsort ('sem_locations').
Es soll je Seminar nur eine Zeile dargestellt werden, dies erreiche ich mit GROUP BY seminar.id.

Jetzt stehen in der 'sem_orders' Tabelle Bestellungen zu den einzelnen Seminaren. Diese können von unterschiedlicher Art sein. Im Feld `archiv` kann eine 3 für Warteliste oder eine 6 für Voranmeldung stehen.

Ich möchte nun zu jedem Seminar die Gesamtanzahl der jeweiligen Bestellungen auflisten. Also alle Einträge in sem_orders zu einem Seminar herausfinden über das Feld `nr` in sem_orders und seminar und deren gesamtanzahl also die summe von `number` (hier steht jeweils die Teilnehmeranzahl je Bestellung drin).

Da es zwei Arten gibt soll innerhalb der Anfrage unterschieden werden, das erreiche ich durch archiv = 3 bzw archiv = 6.

Das Problem ist sobald es 2 Dozenten gibt wird die Anzahl der Bestellungen verdoppelt. d.h. dem seminar werden zwei dozenten zugewiesen und dennen beiden jeweils die bestellungen, somit entsteht die Verdoppelung.


Danke!!!!!!!!

[bubzilla]

hier nochmal mein ansatz:

SELECT *, SUM(os.number) as waiting , SUM(os_p.number) as preview

suche diveres(*), die summe aller wartelisteneinträge als waiting und die summe aller voranmeldungen als preview.

FROM `seminar` AS sem
LEFT JOIN (`sem_locations` AS loc) ON ( sem.`location` = loc.`id` )

aus seminar verbunden mit dem seminarort

LEFT JOIN (order_seminar as os) ON (os.nr = sem.nr AND os.archiv = 3)

verbunden mit allen bestellungen der warteliste

LEFT JOIN (order_seminar as os_p) ON (os_p.nr = sem.nr AND os_p.archiv = 6)

verbunden mit allen bestellungen der voranmeldung

LEFT JOIN (`dozSemRef` AS dozRef) ON ( sem.`id` = dozRef.`seminarId` )
LEFT JOIN (`dozent` AS doz) ON ( dozRef.`dozentId` = doz.`id`)

verbunden mit allen dozenten des seminars

WHERE sem.release <= NOW()
AND end > NOW()
AND sem.archiv IN(0,2,3,4,6) 
GROUP BY sem.id

hier noch ein paar bedingungen die das seminar angehen (veröffentlichungstermin ...)

[Nightflyer]

Aaalso, die Lösung ist vergleichsweise einfach: Mach einfach zwei Queries.

In der ersten Query fragst du das Seminar, seine Dozenten und seinen Standort ab.

Im zweiten Query kannst du nun die Bestellungen zu diesem Seminar abfragen.

[bubzilla]

grundsätzlich stimmt das, aber ich hab das problem das ich das ganze als tabelle darstellen will und deshalb alles in einer abfrage haben möchte um die tabelle nach spalten sortieren zu können!

[Nightflyer]

Das mag sein aber du hast keine Möglichkeit die Duplikation zu verhindernd und gleichzeitig alle Dozenten anzuzeigen. Natürlich kannst du GROUP BY `dozSemRef`.`dozentID` machen aber dann hast ja nur noch einen Dozenten.