Coder Forum

SQL Code

CREATE TABLE `categories` (
  `id` int(11) NOT NULL DEFAULT '0',
  `sub` int(11) NOT NULL DEFAULT '0',
  `name` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
INSERT INTO `categories` (`id`, `sub`, `name`) VALUES
(1, 0, 'Bücher'),
(2, 1, 'Romane'),
(3, 1, 'Biografien'),
(4, 1, 'Geistiger Abfall'),
(5, 2, 'Krimis');

SQL Code

SELECT (COUNT(parent.id)-1) AS depth, node.id
FROM tree AS node, tree AS parent
WHERE node.l BETWEEN parent.l AND parent.r
GROUP BY node.id ORDER BY node.l;

SQL Code

CREATE TABLE `sj_forums` (
  `id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `sub` int(11) NOT NULL DEFAULT '0',
  `pos` int(11) NOT NULL DEFAULT '0',
  `list_subforums` tinyint(1) NOT NULL DEFAULT '1',
  `list_topics` tinyint(1) NOT NULL DEFAULT '1',
  `stat_topics` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `stat_posts` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `title` text collate latin1_general_ci NOT NULL,
  `desc` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Source code

SELECT
  A.`id` AS `id`,
  SUM(`stat_topics`) AS `sum_stat_topics`,
  SUM(`stat_posts`) AS `sum_stat_posts`
FROM `sj_forums` A
LEFT JOIN `sj_forums` B ON B.id = A.sub
LEFT JOIN `sj_forums` C ON C.id = B.sub
WHERE A.`sub`=0
GROUP BY `id`

Source code

SELECT `id`, SUM(B.`hits`)
FROM `categories` A, `categories` B, `categories` C
WHERE A.`sub`=0
LEFT JOIN B.`sub`=A.`id`
LEFT JOIN C.`sub`=B.`id`
GROUP BY `id`

Source code

-- phpMyAdmin SQL Dump
-- version 2.11.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 25. Dezember 2007 um 23:59
-- Server Version: 5.0.45
-- PHP-Version: 5.2.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Datenbank: `test`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `categories`
--

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `sub` int(11) NOT NULL default '0',
  `hits` int(10) unsigned NOT NULL default '0',
  `title` text collate latin1_general_ci NOT NULL,
  `text` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

--
-- Daten für Tabelle `categories`
--

INSERT INTO `categories` (`id`, `sub`, `hits`, `title`, `text`) VALUES
(1, 0, 0, 'Spielen nach Noten', 'Wenn sie bla bla können sie hier ja weitermachen\r\nhier das bla bla usw...'),
(0, -1, 0, 'Gitarrenkurs für Anfänger', 'Hier bla bla Gitarren bla\r\nFahren sie fort mit Spielen nach Noten bla\r\nwenn bla'),
(2, 0, 0, 'Spielen nach Gefühl', 'Ich muss euch ab hier enttäuschen.\r\nIch bin ein lügner, ich kann selbst nicht Gitarre spielen...');

Source code

SELECT
    A.id AS `id`,
    A.title AS `title`,
    SUM(A.stat_posts) + SUM(B.stat_posts) + SUM(C.stat_posts) AS `sum_stat_posts`
FROM `sj_forums` A, `sj_forums` B, `sj_forums` C
WHERE A.sub=0 AND B.sub=A.id AND C.sub=B.id
GROUP BY A.id

Source code

SELECT
    `id`, SUM(`stat_posts`) + (
         SELECT SUM(`stat_posts`) FROM `sj_forums` A WHERE A.`sub`=Z.id
    ) AS `sum_stat_posts`
FROM `sj_forums` Z WHERE `sub`=0
GROUP BY `id`

Source code

SELECT
    `id`, SUM(`stat_posts`) + (
        SELECT SUM(`stat_posts`) FROM `sj_forums` A WHERE A.`sub`=Z.`id`
    ) AS `absstat_posts`
FROM `sj_forums` Z
WHERE `sub`=1
GROUP BY `id`;

Source code

SELECT
    `id`, SUM(`stat_posts`) + (IFNULL(
        SELECT SUM(`stat_posts`) FROM `sj_forums` A WHERE A.`sub`=Z.`id`
    ), 0) AS `absstat_posts`
FROM `sj_forums` Z
WHERE `sub`=1
GROUP BY `id`

SQL Code

SELECT
    `id`, `stat_posts` + IFNULL(
        (SELECT SUM(`stat_posts`) FROM `sj_forums` A WHERE A.`sub`=Z.`id`)
    , 0) AS `absstat_posts`
FROM `sj_forums` Z
WHERE `sub`=0
GROUP BY `id`