Complexe Jointure SQL avec 5 tables

Je suis en train de travailler sur un nombre énorme d'application, qui contient beaucoup de tables. Je dois écrire une requête SQL qui impliquent, après simplification, 5 tables (voir le jpg pour les jointures).

L'idée est la suivante:
Les personnes ont des adresses et des adresses ont un type (privé, pro, etc) et un pays.
Les personnes peuvent également avoir des options. Ces options (illustré ici dans l'option de la table avec une name_id) peut être liée à un type d'adresse.

L'idée est d'extraire toutes les personnes qui ont une ou plusieurs adresses qui sont spécifiés par pays ET par le fait qu'ils apparaissent également dans le [option adresse] de la table.

Par exemple, disons que nous voulons que les personnes qui ont une adresse avec country_id=1. Le jeu de résultats doit exclure les personnes qui n'ont pas le même type d'adresse liée à leurs options.

Bien... je ne suis pas sûr que je comprends moi-même 🙂

Mais de toute façon, voici le code SQL pour créer toutes les choses.

CREATE TABLE `address` (
`person_id` int(11) NOT NULL,
`type_id` int(11) NOT NULL,
`country_id` int(11) NOT NULL,
UNIQUE KEY `apt` (`person_id`,`type_id`),
KEY `apid` (`person_id`),
KEY `atid` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `address` (`person_id`, `type_id`, `country_id`) VALUES
(1, 1, 1),
(2, 2, 1),
(3, 1, 1),
(3, 2, 2),
(5, 1, 2),
(6, 2, 1),
(7, 1, 1),
(7, 2, 2),
(8, 1, 1),
(9, 2, 1);
CREATE TABLE `address_type` (
`id` int(11) NOT NULL,
UNIQUE KEY `tid` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `address_type` (`id`) VALUES
(1),
(2);
CREATE TABLE `option` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name_id` int(11) NOT NULL,
`person_id` int(11) NOT NULL,
UNIQUE KEY `oid` (`id`),
UNIQUE KEY `onp` (`name_id`,`person_id`),
KEY `opid` (`person_id`),
KEY `on` (`name_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
INSERT INTO `option` (`id`, `name_id`, `person_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 5),
(5, 1, 6),
(6, 1, 7),
(7, 1, 8),
(8, 1, 9);
CREATE TABLE `option_address_type` (
`option_id` int(11) NOT NULL,
`type_id` int(11) NOT NULL,
UNIQUE KEY `ot` (`option_id`,`type_id`),
KEY `ooid` (`option_id`),
KEY `otid` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `option_address_type` (`option_id`, `type_id`) VALUES
(1, 1),
(2, 2),
(3, 1),
(3, 2),
(4, 2),
(5, 1),
(6, 1),
(7, 1),
(7, 2),
(8, 1),
(8, 2);
CREATE TABLE `person` (
`id` int(11) NOT NULL,
UNIQUE KEY `pid` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `person` (`id`) VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9);
ALTER TABLE `address`
ADD CONSTRAINT `address_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `address_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `address_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `option`
ADD CONSTRAINT `option_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `option_address_type`
ADD CONSTRAINT `option_address_type_ibfk_1` FOREIGN KEY (`option_id`) REFERENCES `option` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `option_address_type_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `address_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
+1 pour la création de la table de script, avec insertion de données afin que les gens peuvent jouer avec elle. Les bonnes choses
Quels sont les résultats escomptés si le pays = 1 -- dont les gens devraient retour?
Toujours agréable d'avoir un script de création de table (surtout la FK références). Personnellement aurais préféré "non-clé' colonnes, juste pour des exemples. mySQL pour les SGBDR? ...Je pense que votre schéma de base de données est maladroitement mis ensemble, si. Entre autres choses, l'existence d'un address enregistrement implique l'existence d'une adresse-option (qui pourrait être retiré de la table)? Qu'est-ce que option.name_id référence? Ce n'option_address_type.type_id?
name_id est un lien vers une autre table, que je n'ai pas de décrire ici. Et non, une personne ne peut avoir qu'une adresse sans option. Mais une personne peut avoir une option, qui peut être lié à un type d'adresse.

OriginalL'auteur rekam | 2013-01-31