MySQL join table only to find missing values from previous join

Question

Sorry if my title is odd, but I'm not even sure how to word my problem in this description let alone a short title.

We have 1000 users. 400 of them are new. 500 of them have updated their profiles with the new fields we added. 100 have not updated their profiles.

When I try to pull data on a specific field I get 900 results.

Select j1.question, j1.response
FROM table1 t1
JOIN table2 j1 on t1.id_user = j1.iduser AND j1.idquestion IN (26)

This is missing the 100 users that haven't updated their profile using the new profile questions.

When I try to pull data on that specific field to include the old profile question that was similar I get 1500 results.

Select j1.question, j1.response
FROM table1 t1
JOIN table2 j1 on t1.id_user = j1.iduser AND (j1.idquestion IN (26) OR j1.idquestion IN (8))

This pulls the 900 results from 26 as well as the original 600 users result from 8.

So my question is, how do I only get the data of the idquestion IN (26) and then the 100 left over from idquestion IN (8)?

left join and it would appear as null instead of not appear at all — eric.itzhak
– eric.itzhak, Commented Nov 20, 2013 at 15:38

oerkelens · Accepted Answer · 2013-11-20 15:57:44Z

2

This will get you the 100 users that where 'missing' in your first query. I am not sure I understand what you want with quesionID(8).

         SELECT t1.question, t1.response
           FROM table1 t1
LEFT OUTER JOIN table2 j1 on t1.id_user = j1.iduser 
            AND j1.idquestion IN (26)
          WHERE j1.iduser IS NULL

edited Nov 20, 2013 at 15:57

answered Nov 20, 2013 at 15:42

oerkelens

5,1711 gold badge25 silver badges30 bronze badges

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

6 Comments

dannycodes Over a year ago

questionid 26 is "What is your Profession" and questionid 8 is "What is your specialty(old)" So they are two different questions but fit the same demographics section of our report.

ypercubeᵀᴹ Over a year ago

Nice but they would want to change the select into SELECT t1.id_user. Otherwise they'll get 100 rows with Nulls.

oerkelens Over a year ago

@dannycodes, ok, nice. But what exactly do you want as a result now? :) Initially i thought you wanted these 100 "missing" users, but for that i don't need question 8 (or 72 for that matter) :)

dannycodes Over a year ago

@oerkelens I need all 1000 entries as the result. So the 900 with 26 and the 100 with 8 in one query.

oerkelens Over a year ago

Ok, and in each entry you want the data from questionid 26 if it exists and otherwise from 8? If that is the case i will haev a look at that in a while :)

|

The Phil Lee · Accepted Answer · 2013-11-20 17:00:47Z

SELECT IF(q26.question IS NOT NULL, q26.question, q8.question) as question, IF(q26.response IS NOT NULL, q26.response, q8.response) as response
FROM table1 t1
LEFT JOIN table2 as q26
ON 
(t1.id_user = q26.iduser
AND q26.idquestion = 26)
LEFT JOIN table2 as q8
ON
(t1.id_user = q8.iduser
AND q8.idquestion = 8);

This should work. Starting with table1 and left joining ensures you get one answer for each user. Joining q26 will join the q26 values if they exist and null otherwise. Joining q8 will do the same in additional columns.

You end up with table1 with some columns that only apply to question26 (or null), followed by columns that only apply to question 8 (or null). Then, if you use IF() in your selects, you can choose the right columns.

Collectives™ on Stack Overflow

MySQL join table only to find missing values from previous join

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related