MySQL Question
SQL query of multiple joins
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`)
)
The data in the table
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');
My second table userdata:
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`);
The data in it:
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);
And finally the user table :
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 ;
The data :
INSERT INTO `utilisateur` (`id_us`) VALUES
(3),
(6),
(7);
What I want to do is to get the
id_formuserdataI've tried to do it like this:
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
This query leads to this result if we have that kind of data :
id_form
1
6
7
8
9
10
11
12
13
This is the expected result and it's correct. If I want this result for a specific user, I change it like this :
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
I'm getting nothing or it should be like the result that I just wrote.
Let's take another example for
id_us=7SELECT 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
This should result in all
id_form
Answer Source
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 )