SQL query of multiple joins

Question

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.

And you did not get an error on AND id_us=6? You did not state the table alias in there. — Norbert
– Norbert, Commented Jun 14, 2016 at 18:48
Upvote for one of the most thorough questions / detail/ information needed to address the question I've seen in awhile. — xQbert
– xQbert, Commented Jun 14, 2016 at 18:54
You sure that's mysql? d''amorçage is a syntax error. mysql uses \' to escape quotes, not ''. — Marc B
– Marc B, Commented Jun 14, 2016 at 20:00

Thorsten Kettner · Accepted Answer · 2016-06-14 21:11:32Z

3

Right joins are very hard to read and thus prone to errors. Usually you'd start with the table you must get data from and then left outer join tables you might get data from.

Let's look at your query:

You right join after table donnée_utilisateur, so donnée_utilisateur gets outer joined to the other tables.
The other tables are form and utilisateur. You have no join criteria combining the two, so you cross join them, i.e. combine every form with every utilisateur.
So to this cross join product you outer join donnée_utilisateur.
Where d.id_d IS NULL makes this an anti join. A trick used to replace a mere NOT EXISTS or NOT IN in DBMS that have weaknesses with these straight-forward methods. You use it to get all form / utilisateur combinations for which there is no entry in donnée_utilisateur. Probably many.
Where id_us=6 further narrows the results. Unfortunately you forgot to use a qualifier. Is it u.id_us or d.id_us? The DBMS cannot know. Let's say it decides you mean d.id_us. That field is always null, because you just dismissed all matches. d.id_us = 6 is never true, so all rows get discarded. Your result is empty. If the DBMS decided you mean u.id_us, you'd prabably get results, particularly the same id_form over and over.

You may want to add the qualifier u, but I suggest you rather re-write the whole query and use NOT IN or NOT EXISTS.

And what has utilisateur to do with your query anyway? I thought you where looking for forms for which not exists user 6 in donnée_utilisateur. Why join utilisateur at all? (And if you join it, you should probably outer join it to donnée_utilisateur.)

answered Jun 14, 2016 at 21:11

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kamel Mili Over a year ago

So your suggesting i don't use joins i use not in , not exist ??

Thorsten Kettner Over a year ago

Yes. Make it a habit to use [NOT] IN/EXISTS when checking whether data exists or not. Don't use the anti-join pattern if not forced to do so. Don't use right joins; when outer-joining use left joins.

ScaisEdge · Accepted Answer · 2016-06-14 21:36:05Z

2

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 )

edited Jun 14, 2016 at 21:36

answered Jun 14, 2016 at 18:50

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

8 Comments

Kamel Mili Over a year ago

Thank you for answering but i think this doesn t solve my user issue

ScaisEdge Over a year ago

I have slightly update teh answer with id_us = 6 ... i think the at least the first should be right .. why these don't sovel your issuse? .. please explain me ..

Kamel Mili Over a year ago

this has the same result for id_us=6 or 7 or even 12 am trying to get the values that doesn't exsit inside donnée_utilisateur your code has something like this as a result

Kamel Mili Over a year ago

id_form = 1,2,3,..,12

ScaisEdge Over a year ago

Based on the date you provided like sample the select in update question must provide only the form_id in form that are not in donnée_utilisateur with id_us=6 .. if is not check your data or your schema

|

DRapp · Accepted Answer · 2016-06-14 22:27:10Z

Thorsten was very good in his clarification, but did not provide the completed query to help you. Your original right-join query was VERY close. However, I have switched to a left-join as follows:

SELECT
      f.id_form,
      f.nameform
   from
      form f
         left join donnée_utilisateur d
            ON f.id_form = d.id_form 
            AND d.id_us = 6
   where 
      d.id_d IS NULL

So, I am starting with the FORM table to get the ID and the name. No problem. Now, your consideration that the form is not found within the secondary table, so that is a left-join on the form ID and looking for NULL in the where clause. But this, by itself is qualifying a form for ANY user. To finalize your need for a specific user, just add the AND clause to the secondary table so THAT portion remains as a left-join for the FORM AND specific user resulting in NULL for the d.id_d column

Collectives™ on Stack Overflow

SQL query of multiple joins

3 Answers 3

2 Comments

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related