'How to merge multiple rows into columns using left joins
I'm currently stuck trying to write a query in MS Access using multiple left joins.
It should merge the results of a query with the data of an existing table. Table "Adressen" contains an unique identifier which should be used for the LEFT JOIN. "q_TestMatchen" however has multiple rows per uuid, one for each [betreiber], which means that there can be uuids where no data for [betreiber] = "Carrier1" is available.
Eventually I want to have one row per [uuid] with the matched columns of "q_TestMatchen" appended at the end.
I hope that this makes sense so far.
Below is my current code:
SELECT q.*, v.betreiber, v.ist_down, v.ist_up, v.ist_tech, v.plan_down, v.plan_up, t.betreiber, t.ist_down, t.ist_up, t.ist_tech, t.plan_down, t.plan_up
FROM (Adressen AS q
LEFT JOIN q_TestMatchen AS v ON q.uuid = v.uuid)
LEFT JOIN q_TestMatchen AS t ON q.uuid = t.uuid
WHERE (q.[betreiber] IS NULL AND IIF(v.[betreiber] IS NOT NULL, v.[betreiber] = "Carrier1", 1) AND t.[betreiber] = "Carrier2") AND q.fa <> 0;
This is what the result of the input query "q_TestMatchen" looks like (same columns as in table "Adressen", but the fields 'betreiber', 'ist_down' etc. are empty there):
| uuid | x | y | .... | betreiber | ist_down | ist_up | ..... |
|---|---|---|---|---|---|---|---|
| DETEST1234 | 23.4567 | 45.2345 | .... | carrier1 | 250 | .... | |
| DETEST1234 | 23.4567 | 45.2345 | .... | carrier2 | 1000 | .... | |
| DETEST1235 | 23.6789 | 45.3456 | .... | carrier2 | 1000 | .... | |
| DETEST1236 | 23.3456 | 45.1523 | .... | carrier1 | 500 | .... | |
| DETEST1236 | 23.3456 | 45.1523 | .... | carrier2 | 100 | .... |
This is what I want the table to look like in the end:
| uuid | x | y | .... | v.betreiber | v.ist_down | .... | t.betreiber | t.ist_down | ..... |
|---|---|---|---|---|---|---|---|---|---|
| DETEST1234 | 23.4567 | 45.2345 | .... | carrier1 | 250 | .... | carrier2 | 1000 | .... |
| DETEST1235 | 23.6789 | 45.3456 | .... | .... | carrier2 | 1000 | .... | ||
| DETEST1236 | 23.3456 | 45.1523 | .... | carrier1 | 500 | .... | carrier2 | 100 | .... |
The code above is working, but it generates two rows per uuid, which is not what i eventually want it to be.
Thanks for your help in advance. Best Regards, Michael
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
