We help IT Professionals succeed at work.

MySQL join two tables nulls on both tables

hbojorquezgeek
hbojorquezgeek used Ask the Experts™
on
I think I'm asking the question right here are the tables and hoped for results
 
Table 1
StandardQuestions
|      QID      |       QuestionType             | Question
|      1      |      GeneralKnowledge            | Who is President of   a?
|      2      |      GeneralKnowledge            | Who is President of  b ?
|      3      |      GeneralKnowledge            | Who is President of  c?
|      4      |      GeneralKnowledge            | Who is President of  d ?
|      5      |      GeneralKnowledge            | Who is President of  e ?
|      6      |      GeneralKnowledge            | Who is President of  f ?



Table2
ChosenQuestions
|      ChID|       QID         |      Question
|      1      |      4            | Who is President of  d ?
|      2      |      5            | Who is President of  e ?
|      3      |      6            | Who is President of  f ?
|      4      |      NULL      | Who is President of  Mumbai?



Result
CHID      |      QID      |       QuestionType             | Question                   
NULL      |      1      |      GeneralKnowledge            | Who is President of   a?  
NULL      |      2      |      GeneralKnowledge            | Who is President of  b ?
NULL      |      3      |      GeneralKnowledge            | Who is President of  c?                      
1              |      4      |      GeneralKnowledge            | Who is President of  d ?    
2              |      5      |      GeneralKnowledge            | Who is President of  e ?|
3              |      6      |      GeneralKnowledge            | Who is President of  f ?
4              |      NULL|      NULL                        | Who is President of  Mumbai?


Table 1 Questions are pretty immutable- rarely change- user chose them into Table 2- They also add their OWN questions into table 2 - The NULL QID  denotes that it wasn't chosen from Table 1.

I need a summary table that includes ALL the Table 1 questions- with NULLS in the CHID column  to show they  weren't chosen , and a NULL in   the QUID for questions from Table 2 that were entered by users.

I would not have designed these tables like this but I've inherited the schema.

 
Please help,,,,,, I'm super desperate- I'm using MySQL
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
by looking at your result, you just need to use UNION  ALL / UNION to get all info

select  QID,   ChID, QuestionType, Question
FROM table1
UNION
select  QID,   ChID, QuestionType, Question
FROM table2

OR

select  QID,   ChID, QuestionType, Question
FROM table1
UNION ALL
select  QID,   ChID, QuestionType, Question
FROM table2

Author

Commented:
I looked into that earlier today and I ran into the issue that both table don't have the same columns... but let me try again.

Commented:
Ok, you can not do Union query without matching columns in both tables

Author

Commented:
DANG I failed to mention one other piece to this... I'm sorrry-  but Table 2 has a Users column so I would need to get the results like a list for a specific user--

Result
Usr |CHID      |      QID  |       QuestionType                  | Question                  
4     |NULL      |      1      |      GeneralKnowledge            | Who is President of   a?  
4     |NULL      |      2      |      GeneralKnowledge            | Who is President of  b ?
4     |NULL      |      3      |      GeneralKnowledge            | Who is President of  c?                      
4     | 1            |      4      |      GeneralKnowledge            | Who is President of  d ?    
4     | 2            |      5      |      GeneralKnowledge            | Who is President of  e ?|
4     | 3            |      6      |      GeneralKnowledge            | Who is President of  f ?
4     | 4            |     NULL|      NULL                                | Who is President of  Mumbai?

Commented:
try this:


SELECT IIf( table2.[QID] Is Null,Null, table2.[QID]) AS Expr1, getNull.QuestionType, getNull.Question, getNull.ChID
FROM getNull LEFT JOIN table2 ON getNull.QID = table2.QID;

Author

Commented:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[QID] Is Null,Null, table2.[QID]) AS Expr1' at line 1
(0 ms taken)

I'm a little confused -- Expr1- is that supposed to be getNull
and there's no mention of table 1

Thanks
Technology Scientist
Commented:
just try:
LEFT OUTER JOIN
SELECT b.Usr, b.CHID, b.QID, a.* FROM Table2 b 
LEFT OUTER JOIN 
Table1 a
ON a.QID= b.QID

Open in new window

Author

Commented:
@Nerds- I'm working on this-- that may help
NerdsOfTechTechnology Scientist

Commented:
Let me know if that worked for you.

Author

Commented:
I have to be out of town for a few days -  Ill come back to check this
NerdsOfTechTechnology Scientist

Commented:
mod cleanup
accepted http:#33149474