Hi ,
It's been three days that I am lost in the code.
In the table film_actor , i add moduleid
Here is what I want to do:
When we select module 1 : Show only actors who are not enrolled in the module 1
SELECT actor_id FROM actor
WHERE actor_id NOT INT
(
SELECT * FROM `film_actor` WHERE moduleid <> 1
)
When we select module 2 :
SELECT actor_id FROM actor
WHERE actor_id NOT INT
(
SELECT * FROM `film_actor` WHERE moduleid <> 2
)
At the end also insert the moduleid in the table film_actor
-- Structure de la table `module`
--
CREATE TABLE IF NOT EXISTS `module` (
`moduleid` int(8) NOT NULL AUTO_INCREMENT,
`moduledescription` varchar(60) DEFAULT NULL,
PRIMARY KEY (`moduleid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
--
-- Contenu de la table `module`
--
INSERT INTO `module` (`moduleid`, `moduledescription`) VALUES
(1, 'Module 1'),
(2, 'Module 2'),
(3, 'Module 3'),
(4, 'Module 4'),
(5, 'Module 5');
-- Structure de la table `film_actor`
CREATE TABLE IF NOT EXISTS `film_actor` (
`actor_id` smallint(5) unsigned NOT NULL,
`film_id` smallint(5) unsigned NOT NULL,
`priority` int(11) NOT NULL,
`moduleid` int(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Contenu de la table `film_actor`
--
INSERT INTO `film_actor` (`actor_id`, `film_id`, `priority`, `moduleid`) VALUES
(2, 1013, 0, 1),
(3, 1013, 1, 1),
(204, 1013, 2, 0),
(206, 1013, 3, 0),
(3, 1015, 0, 0);
In the attachment more information
Thanks