✔ Tabellenabfrage liefert keine Ergebnisse

[ByeBye 182971]

Hi,

das Problem liegt glaub ich an dem COUNT, da nur die Artikel angezeigt werden, welche auch einen Kommentar haben.

Ziel meiner Abfrage ist alle Artikel zu holen und dazu eine Angabe über Kathegorie und Kommentare zu geben. So wie es immer bei Blogs ist

Erstmal die Abfrage:

SELECT
	ca.title AS category,
	ar.*,
	DATE(ar.date) AS date,
	COUNT(*) AS comments
FROM
	articles AS ar,
	categories AS ca,
	comments AS co
WHERE
	ca.url_title = 'frameworks'
AND
	ar.category_id = ca.id
AND
	co.article_id = ar.id
GROUP BY co.article_id
ORDER BY
	date DESC;

Die dazugehörigen Tabellen:

CREATE TABLE `articles` (
  `id` int(11) NOT NULL auto_increment,
  `text` text collate latin1_general_ci NOT NULL,
  `title` varchar(112) collate latin1_general_ci NOT NULL,
  `url_title` varchar(112) collate latin1_general_ci NOT NULL,
  `author` varchar(112) collate latin1_general_ci NOT NULL,
  `date` datetime NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=11 ;

CREATE TABLE `categories` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(112) collate latin1_general_ci NOT NULL,
  `url_title` varchar(112) collate latin1_general_ci NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=10 ;

CREATE TABLE `comments` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(112) collate latin1_general_ci NOT NULL,
  `text` text collate latin1_general_ci NOT NULL,
  `ip` varchar(112) collate latin1_general_ci NOT NULL,
  `article_id` int(11) NOT NULL,
  `date` varchar(112) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4 ;

 

[ByeBye 182971]

Habe es wiedermal selbst gelöst.

Lösung liegt bei LEFT JOIN:

 SELECT ca.title AS category, ar. * , DATE( ar.date ) AS date, COUNT( co.id ) AS comments
FROM articles AS ar
LEFT JOIN categories AS ca ON ca.url_title = "seo"
LEFT JOIN comments AS co ON co.article_id = ar.id
WHERE ar.category_id = ca.id
GROUP BY co.article_id
ORDER BY date DESC
LIMIT 0 , 30

 

[Nico Graichen]

Das ist schön
Markierst du deinen Thread dann bitte noch als erledigt? Danke