Subselect, Havin, Count, Join... ich weisz es nicht ;-(

[Peter Nack]

Hallo Peter ;-)

erstmal Danke fuer deine  Interesse!

Ich habe die DB jetzt mal auf das minimalste kastriert.
Es existieren drei Resorts, wobei "Nochmal alles" und "Alles Resort" a) alle ResortFacilities und b) mindestens einer deren Rooms alle RoomFacilities besitzt.

  
-- phpMyAdmin SQL Dump  
-- version 2.11.9.2  
-- http://www.phpmyadmin.net  
--  
-- Host: localhost  
-- Generation Time: Dec 02, 2009 at 11:15 AM  
-- Server version: 5.0.67  
-- PHP Version: 5.2.6  
  
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";  
  
--  
-- Database: `resorts`  
--  
  
-- --------------------------------------------------------  
  
--  
-- Table structure for table `mm_resort_facility`  
--  
  
CREATE TABLE IF NOT EXISTS `mm_resort_facility` (  
  `id` int(11) NOT NULL auto_increment,  
  `resort_id` int(11) NOT NULL,  
  `resort_facility_id` int(11) NOT NULL,  
  PRIMARY KEY  (`id`),  
  KEY `fk_mmrf_resort` (`resort_id`),  
  KEY `fk_mmrf_resort_facility` (`resort_facility_id`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ;  
  
--  
-- Dumping data for table `mm_resort_facility`  
--  
  
INSERT INTO `mm_resort_facility` (`id`, `resort_id`, `resort_facility_id`) VALUES  
(16, 7, 1),  
(17, 7, 2),  
(18, 7, 3),  
(19, 7, 4),  
(20, 7, 5),  
(21, 7, 6),  
(22, 7, 7),  
(23, 8, 1),  
(24, 8, 2),  
(25, 8, 3),  
(26, 8, 4),  
(27, 8, 5),  
(28, 8, 6),  
(29, 8, 7),  
(30, 9, 1),  
(31, 9, 2),  
(32, 9, 3),  
(33, 9, 4),  
(34, 9, 5),  
(35, 9, 6),  
(36, 9, 7),  
(37, 5, 1),  
(38, 5, 2),  
(39, 5, 3),  
(40, 5, 4),  
(41, 5, 5),  
(42, 5, 6),  
(43, 5, 7),  
(44, 1, 3),  
(45, 1, 4),  
(46, 1, 5),  
(47, 1, 6),  
(48, 6, 3),  
(49, 6, 4),  
(50, 6, 5),  
(51, 6, 6);  
  
-- --------------------------------------------------------  
  
--  
-- Table structure for table `mm_room_facility`  
--  
  
CREATE TABLE IF NOT EXISTS `mm_room_facility` (  
  `id` int(11) NOT NULL auto_increment,  
  `room_id` int(11) NOT NULL,  
  `room_facility_id` int(11) NOT NULL,  
  PRIMARY KEY  (`id`),  
  KEY `fk_rf_room` (`room_id`),  
  KEY `fk_rf_room_facility` (`room_facility_id`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;  
  
--  
-- Dumping data for table `mm_room_facility`  
--  
  
INSERT INTO `mm_room_facility` (`id`, `room_id`, `room_facility_id`) VALUES  
(17, 8, 1),  
(18, 8, 4),  
(19, 8, 2),  
(20, 9, 1),  
(21, 9, 4),  
(22, 9, 2),  
(23, 10, 1),  
(24, 10, 4),  
(25, 10, 2),  
(26, 11, 1),  
(27, 11, 4),  
(28, 11, 2),  
(29, 12, 1),  
(30, 12, 4),  
(31, 12, 2);  
  
-- --------------------------------------------------------  
  
--  
-- Table structure for table `resort`  
--  
  
CREATE TABLE IF NOT EXISTS `resort` (  
  `id` int(11) NOT NULL auto_increment,  
  `name` varchar(255) NOT NULL,  
  PRIMARY KEY  (`id`),  
  UNIQUE KEY `u_name_location` (`name`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;  
  
--  
-- Dumping data for table `resort`  
--  
  
INSERT INTO `resort` (`id`, `name`) VALUES  
(8, 'Alles Resort'),  
(9, 'Nochmal Alles'),  
(6, 'Sea Quest Center');  
  
-- --------------------------------------------------------  
  
--  
-- Table structure for table `resort_facility`  
--  
  
CREATE TABLE IF NOT EXISTS `resort_facility` (  
  `id` int(11) NOT NULL auto_increment,  
  `name` varchar(64) NOT NULL,  
  PRIMARY KEY  (`id`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;  
  
--  
-- Dumping data for table `resort_facility`  
--  
  
INSERT INTO `resort_facility` (`id`, `name`) VALUES  
(1, 'Internet'),  
(2, 'Loundry'),  
(3, 'Scuba'),  
(4, 'Handicapped'),  
(5, 'Daytrips'),  
(6, 'Aircon'),  
(7, 'CarRental');  
  
-- --------------------------------------------------------  
  
--  
-- Table structure for table `room`  
--  
  
CREATE TABLE IF NOT EXISTS `room` (  
  `id` int(11) NOT NULL auto_increment,  
  `resort_id` int(11) NOT NULL,  
  `name` varchar(255) NOT NULL,  
  PRIMARY KEY  (`id`),  
  KEY `fk_room_resort` (`resort_id`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;  
  
--  
-- Dumping data for table `room`  
--  
  
INSERT INTO `room` (`id`, `resort_id`, `name`) VALUES  
(1, 1, 'Honeymoon-Suit'),  
(2, 4, 'test'),  
(3, 5, 'Honeymoon-Suit'),  
(4, 5, 'Villa Lemon'),  
(5, 6, 'Dream Day'),  
(6, 7, 'Villa Green'),  
(7, 8, 'MyCottage'),  
(8, 8, 'MySuit'),  
(9, 8, 'MyVilla'),  
(10, 9, 'TheCottages'),  
(11, 9, 'TheSuits'),  
(12, 9, 'TheVillas');  
  
-- --------------------------------------------------------  
  
--  
-- Table structure for table `room_facility`  
--  
  
CREATE TABLE IF NOT EXISTS `room_facility` (  
  `id` int(11) NOT NULL auto_increment,  
  `name` varchar(64) NOT NULL,  
  PRIMARY KEY  (`id`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;  
  
--  
-- Dumping data for table `room_facility`  
--  
  
INSERT INTO `room_facility` (`id`, `name`) VALUES  
(1, 'Internet'),  
(2, 'Aircon'),  
(4, 'Handicapped');  
  

Meine Testversuche mit dem Dump liefen eigentlich korrekt ab, so dass ich davon ausgehe, keine groszen Fehler bei der Reduzierung gemacht zu haben.
Falls dennoch Probleme auftreten, bitte meckern ;)

Danke & mfg
Peter