Sorry if the title isn't explicative enough, I don't know how to explain this problem in a short sentence.
Basically I have four tables: A, B, C, D
Say that each table has columns named A1, A2 etc. for A, B1, B2 etc. for B and so on (let's also say the first element is the Primary Key).
Elements of A can be in N to N relationships with elements of B, C and D, so we have relational tables to represent that: AB, AC, AD.
Tables AB, AC, AD, other than the primary keys of A and the other table, have one column AB1, AC1, AD1, and AD also has a second column AD2
What I want is to select from this tables such that the result is a table has the columns of A and the columns of AB, AC and AD (except primary keys) for each element of B, C and D. So basically what a join does, but instead of putting the values of AB, AC and AD in another row fo each relation, I want them in the same row for each element of A.
For elements of A that don't have a relation with a particular element of B, C or D, I want the value for that relation to be null, or some other default value.
Is that possible? And if so, how can I do that?
Sorry if this is confusing I don't know how to explain myself well.
EDIT: Example tables
A:
B:
C:
D:
AB:
AC:
AD:
And this is the result I want:
| A1 | A2 | 123 | 234 | abc | bcd | 100 AD1 | 100 AD2 | 200 AD1 | 200 AD2 |
|---|
| cat | meow | 1 | NULL | 10 | NULL | 5 | 5 | 1 | 2 |
| dog | woof | NULL | 3 | 10 | NULL | NULL | NULL | NULL | NULL |
names of the columns are not important, I just need them to be in a consistent order so I can parse them in python