Kamel Mili Kamel Mili - 5 months ago 22
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_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.

Answer

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 )
Comments