'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