Start Free Trial

asked on

MySQL join two tables nulls on both tables

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

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

ASKER

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.

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

ASKER

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?

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;

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

@Nerds- I'm working on this-- that may help

Let me know if that worked for you.

ASKER

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

mod cleanup
accepted http:#33149474