Let's say we have 2 tables:
t1: with columns t1.doc_id, ... looks like this:
| t1.doc_id |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
t2: with columns t2.from_doc_id, t2.to_doc_id, t2.flag, ... looks like this
| from_doc_id | to_doc_id | flag |
|---|---|---|
| 1 | 100 | null |
| 1 | 101 | null |
| 2 | 100 | null |
| 3 | 8 | set |
I wanted to get a query result like this:
| t1.doc_id | refs |
|---|---|
| 1 | 100, 101 |
| 2 | 100 |
| 3 |
Basically, wherever flag is null, i want to collect all to_doc_id and concatenate them in a string if flag is not null. Like the first two results in the example above.
If flag is not null (like third row in t2 example), I still want to get the 3 in the query result, but the refs field should not contain 8.
with a query:
SELECT t1.doc_id, group_concat(t2.to_doc_id) AS refs
FROM t1
LEFT OUTER JOIN t2 ON t2.from_doc_id = t1.id
WHERE t2.flag is null
... i miss the last line entirely, I only get the first two lines from the expected result.
So I guess, what I want, is to use WHERE t2.flag is null in group_concat, but to ignore the t2.flag when I get the t1.doc_id values.
You can use CASE inside GROUP_CONCAT
SELECT from_doc_id
,group_concat(CASE WHEN flag IS NULL THEN to_doc_id ELSE '' END) AS refs
FROM yourtable t1
JOIN yourtable2 t2 ON t1.doc_id = t2.from_doc_id
GROUP BY from_doc_id