I have a problem dealing with joins
This is my first table:
CREATE TABLE IF NOT EXISTS `form` (
`id_form` int(20) NOT NULL AUTO_INCREMENT,
`nameform` varchar(50) NOT NULL,
PRIMARY KEY (`id_form`)
)
INSERT INTO `form` (`id_form`, `nameform`) VALUES
(1, 'Formulaire commun'),
(2, 'Formulaire FCPR'),
(3, 'Formulaire fonds d''amorçage'),
(4, 'Formulaire FOPRODI'),
(5, 'Formulaire ITP'),
(6, 'Formulaire PASRI'),
(7, 'Formulaire PCAM'),
(8, 'Formulaire PIRD'),
(9, 'Formulaire PMN'),
(10, 'Formulaire PNRI'),
(11, 'Formulaire PRF'),
(12, 'Formulaire RIICTIC'),
(13, 'Formulaire VRR');
CREATE TABLE IF NOT EXISTS `donnée_utilisateur` (
`id_d` int(20) NOT NULL AUTO_INCREMENT,
`id_form` int(20) NOT NULL,
`id_us` int(20) NOT NULL,
PRIMARY KEY (`id_d`),
KEY `id-form` (`id_form`),
KEY `id-us` (`id_us`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=407 ;
ALTER TABLE `donnée_utilisateur`
ADD CONSTRAINT `fvdsvsd` FOREIGN KEY (`id_us`) REFERENCES `utilisateur` (`id_us`),
ADD CONSTRAINT `ssssssssssss` FOREIGN KEY (`id_form`) REFERENCES `form` (`id_form`);
INSERT INTO `donnée_utilisateur` (`id_d`, `id_form`, `id_us`) VALUES
(380, 2, 6),
(381, 2, 6),
(382, 3, 6),
(383, 3, 6),
(384, 4, 6),
(385, 5, 6);
CREATE TABLE IF NOT EXISTS `utilisateur` (
`id_us` int(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id_us`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
INSERT INTO `utilisateur` (`id_us`) VALUES
(3),
(6),
(7);
id_form
userdata
SELECT f.id_form
FROM `donnée_utilisateur` d
RIGHT JOIN `form` f ON f.id_form=d.id_form Where d.id_d IS NULL
id_form
1
6
7
8
9
10
11
12
13
SELECT f.id_form
FROM `donnée_utilisateur` d
RIGHT JOIN `form` f ON f.id_form=d.id_form
INNER JOIN `utilisateur` u ON u.id_us=d.id_us Where d.id_d IS NULL AND id_us=6
id_us=7
SELECT f.id_form
FROM `donnée_utilisateur` d
RIGHT JOIN `form` f ON f.id_form=d.id_form
INNER JOIN `utilisateur` u ON u.id_us=d.id_us Where d.id_d IS NULL AND u. id_us=7
id_form
You can do with a subselect
select id_form from form where
id_form not in (select distinct id_form from donnée_utilisateur where id_us=6 )
Or RDBMS engine correctly
select id_form from form where
id_form not in (select id_form from donnée_utilisateur where id_us=6 )