id name
1  a
2  b

id hauptId filter wert
1  1       0      110
2  1       1      101
3  1       1      102
4  2       0      200

SELECT haupt.id, COUNT(sub.id)
   FROM
      haupt
      INNER JOIN sub ON (sub.hauptId = haupt.id AND sub.filter = 1)
   WHERE haupt.id = 1

SELECT haupt.id, COUNT(sub.id)
   FROM
      haupt
      INNER JOIN sub ON (sub.hauptId = haupt.id)
   WHERE
      haupt.id = 1 AND
      sub.filter = 1

SELECT haupt.id, MAX(sub.wert)
   FROM
      haupt
      INNER JOIN sub ON (sub.hauptId = haupt.id)
   WHERE
      haupt.id = 1

SELECT haupt.id, COUNT(sub0.id), MAX(sub1.wert)
   FROM
      haupt
      INNER JOIN sub AS sub0 ON (sub0.hauptId = haupt.id AND sub0.filter = 1)
      INNER JOIN sub AS sub1 ON (sub1.hauptId = haupt.id)
   WHERE haupt.id = 1

SELECT haupt.id, sub0.id, sub1.id
   FROM
      haupt
      INNER JOIN sub AS sub0 ON (sub0.hauptId = haupt.id AND sub0.filter = 1)
      INNER JOIN sub AS sub1 ON (sub1.hauptId = haupt.id)
   WHERE haupt.id = 1

CREATE TABLE IF NOT EXISTS `haupt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3 ;
INSERT INTO `haupt` (`id`, `name`) VALUES
(1, 'a'),
(2, 'b');
CREATE TABLE IF NOT EXISTS `sub` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hauptId` int(11) NOT NULL,
  `filter` int(11) NOT NULL,
  `wert` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5 ;
INSERT INTO `sub` (`id`, `hauptId`, `filter`, `wert`) VALUES
(1, 1, 0, 110),
(2, 1, 1, 101),
(3, 1, 1, 102),
(4, 2, 0, 200);