CODEWITHSUNDEEP
mysqlsqljoin
Martin AJ
Martin AJ

Reputation: 6697

How can I join on null?

Here is my table structure:

-- qanda (stands for questions and answers)
+----+---------+-----------------------------------------------+--------------+
| id |  title  |                      content                  |  question_id |
+----+---------+-----------------------------------------------+--------------+
| 1  | title1  | this is a question                            | NULL         |
| 2  | NULL    | this is an answer                             | 1            |
| 3  | NULL    | this is another answer                        | 1            |
| 4  | title2  | this is another question                      | NULL         |
| 5  | NULL    | this is an answer for the second question     | 4            |
| 6  | NULL    | this is another answer for the first question | 1            |
+----+---------+-----------------------------------------------+--------------+

I know, it would be much better if I keep the questions and answers in two different tables. But now I'm just trying to understand how exactly JOIN works in this case.

I have an id of qanda table and I always want a title. That id might be either the id of a question or the id of an answer. How can I do that?

I want something like this:

SELECT t1.title
FROM qanda t1
INNER JOIN qanda t2
ON t1.id = t2.question_id
WHERE t1.id = :id

My query matches nothing. And here are some samples of the expected results:

-- :id = 1
+--------+
| title1 |
+--------+

-- :id = 2
+--------+
| title1 |
+--------+

-- :id = 4
+--------+
| title2 |
+--------+

-- :id = 5
+--------+
| title2 |
+--------+

-- :id = 6
+--------+
| title1 |
+--------+

Upvotes: 0

Views: 116

Answers (3)

xQbert
xQbert

Reputation: 35323

Similar to Serg; but using a left join will allow questions with out (w/o) answers to appear in the results if you have such a situation.

SELECT distinct coalesce(t2.title, t1.title) as title
FROM qanda t1 
LEFT JOIN qanda t2 
  ON t1.id = t2.question_id 
WHERE (t1.id = 1 or T2.ID = 1)
  and Type = 0;

If we can assume a title only exists on questions and no answers will have titles.

I think this is harder to maintain, it should be faster as it eliminates the join (kinda the exists can early escape where the join wouldn't be able to and since the limit occurs on the subquery we only have 1 record really to deal with on the join) and the distinct.

SELECT t1.title as title
FROM qanda t1 
WHERE (EXISTS (SELECT 1 
               FROM qanda t2 
               WHERE ID = 1 
                 and t1.ID = t2.question_id)  --correlated subquery
           or t1.id = 1)
  and Type = 0

Upvotes: 1

etsa
etsa

Reputation: 5060

You can try something like this:

SELECT title 
FROM qanda 
INNER JOIN (
    SELECT DISTINCT COALESCE(t1.question_id, t1.id) AS ID
    FROM qanda t1   
    WHERE :id IN(t1.question_id=:id, t1.id)
    ) B ON qanda.id = B.ID;

Upvotes: 0

Serg
Serg

Reputation: 22811

Union of 2 queries

SELECT t1.title
FROM qanda t1
WHERE t1.id = :id and t1.title IS NOT NULL
UNION
SELECT t1.Title
FROM qanda t2
JOIN qanda t1
ON t1.id = t2.question_id
WHERE t2.id = :id

Alternatively

SELECT DISTINCT t1.title
FROM qanda t1
JOIN qanda t2
ON t1.id = t2.question_id
WHERE :id in ( t2.id, t1.id)

Upvotes: 3

Related Questions