Merging UNION ALL rows to remove NULL values

Question

SELECT journey.id, TIME_FORMAT(ADDTIME(journey.departure, SEC_TO_TIME(SUM(l1.elapsed))), '%H:%i') AS departure, null AS arrival
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.code
INNER JOIN pattern ON pattern.code = journey.pattern
INNER JOIN link l1 ON l1.section = pattern.section AND l1.stop = "370023139"
INNER JOIN link l2 ON l2.section = pattern.section AND l2.id <= l1.id
WHERE journey.service = "11-252-_-y08-1" AND journey_day.day = 1 AND journey.code NOT IN (SELECT journey
                                                                                          FROM journey_non_operation
                                                                                          WHERE "2015-03-01" BETWEEN date_start AND date_end) AND pattern.direction = "outbound"
GROUP BY journey.id

UNION ALL

SELECT journey.id, null AS departure, TIME_FORMAT(ADDTIME(journey.departure, SEC_TO_TIME(SUM(l1.elapsed))), '%H:%i') AS arrival
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.code
INNER JOIN pattern ON pattern.code = journey.pattern
INNER JOIN link l1 ON l1.section = pattern.section AND l1.stop = "1000DEHS7812"
INNER JOIN link l2 ON l2.section = pattern.section AND l2.id <= l1.id
WHERE journey.service = "11-252-_-y08-1" AND journey_day.day = 1 AND journey.code NOT IN (SELECT journey
                                                                                          FROM journey_non_operation
                                                                                          WHERE "2015-03-01" BETWEEN date_start AND date_end) AND pattern.direction = "outbound"
GROUP BY journey.id

Above is two queries with their results merged by a UNION ALL clause. You will notice that the queries return different columns, one called 'departure' and one called 'arrival'. To get the UNION to work with the different column names I have to give the other column NULL so that it doesn't ignore it and not include it in the query.

My issue is I have results that looks like this:

id | departure | arrival
1 asd NULL
2 asd NULL
3 asd NULL
4 asd NULL
5 NULL efg
6 NULL efg
7 NULL efg
8 NULL efg

How can I merge the rows so that asd and efg match up based on the ID?

Desired result:

id | departure | arrival
1 asd efg
2 asd efg
3 asd efg
4 asd efg

@notulysses Updated question with visual desired result table — user3605739, Mar 01 '15 at 16:25
so you want the being join by? journey.id?? or randomly? :-) — Alex, Mar 01 '15 at 16:34

score 4 · Accepted Answer · answered Mar 01 '15 at 16:36

4

Just apply an aggregate function (min/max) to your result set. As nulls are not included in the aggregate you will just get the combined results:

select id, min(departure), min(arrival)
from (your query) as q
group by id

answered Mar 01 '15 at 16:36

slaakso

8,331
2
16
27

score 0 · Answer 2 · answered Mar 01 '15 at 16:39

0

I am not sure why did you try to build union.

Try this way:

SELECT journey.id, 
    TIME_FORMAT(ADDTIME(journey.departure, SEC_TO_TIME(SUM(l1.elapsed))), '%H:%i') AS departure, 
    TIME_FORMAT(ADDTIME(journey.departure, SEC_TO_TIME(SUM(l2.elapsed))), '%H:%i') AS arrival
FROM journey
INNER JOIN pattern 
ON pattern.code = journey.pattern
INNER JOIN link l1 
ON l1.section = pattern.section 
    AND l1.stop = "370023139"
INNER JOIN link l2 
ON l2.section = pattern.section 
    AND l2.stop = "1000DEHS7812"
    AND l2.id <= l1.id
WHERE journey.service = "11-252-_-y08-1" 
    AND journey_day.day = 1 
    AND journey.code NOT IN (
        SELECT journey
        FROM journey_non_operation
        WHERE "2015-03-01" BETWEEN date_start AND date_end) 
    AND pattern.direction = "outbound"
GROUP BY journey.id

answered Mar 01 '15 at 16:39

Alex

16,739
1
28
51

Thanks for your answer but your query will not return the same results. Notice the second `link` INNER JOIN on both queries. This is required in order to calculate the value in the SELECT clause. – user3605739 Mar 01 '15 at 16:41
WHAT? the secod link was ignored in queries before. just try. I just updated the query with `l2.id <= l1.id` that you was highlighted? – Alex Mar 01 '15 at 16:42
I have tried your query, it doesn't return the desired results. Yes, it does merge them and remove the `NULL` problem, but you have missed the `INNER JOIN link l2 ON l2.section = pattern.section AND l2.id <= l1.id` for both queries. – user3605739 Mar 01 '15 at 16:45
that doesn't matter what I had remove from your query. what is count is what it return now? and what is difference from desired result? – Alex Mar 01 '15 at 16:46

Merging UNION ALL rows to remove NULL values

2 Answers2

Linked