mercredi 15 juin 2016

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_form which doesn't exist in userdata table for a specific user.

I'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=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

This should result in all id_form from 1 to 12 because the user didn't insert any data.

Aucun commentaire:

Enregistrer un commentaire