I do not know, how to explain better:
I need a view (in a very normalized matter) that expands the IDs to the corresponding names, so that I need to combine f.e. left joins with inner joins in the following matter (see example)
Tables:
TCUSTOMER
ID| NAME| ANIMAL| CAR| PROFESSION
1| Sheldon| | | 1
2| Leonard| | 1| 1
3| Howard | | 2| 2
4| Radshesh| 1| | 1
5| Penny | | 3|
TANIMAL
ID| NAME| TYPE
1| CINNAMON| 1
TANIMALTYPE
ID| NAME
1| DOG
ID| NAME| TYPE
1| CAL-0123| 1
2| CAL-2345| 1
3| CAL-4567| 2
TCARTYPE
ID| NAME
1| FORD
2| BMW
TPROFESSION
1| PHYSICIAN
2| ENGINEER
and I need a view with the following result:
VCUSTOMER
ID| NAME|ANIMALNAME| ANIMALTYPE| CARNAME|CARTYPE| PROFESSIONNAME
1| Sheldon| | | | | PHYSICIAN
2| Leonard| | |CAL-0123| FORD| PHYSICIAN
3| Howard| | |CAL-2345| FORD| ENGINEER
4|Radshesh| CINNAMON| DOG| | | PHYSICIAN
5| Penny| | |CAL-4567| BMW|
I think I need something like
SELECT
C.ID, C.NAME,
A.NAME, AT.NAME,
CAR.NAME, CART.NAME,
P.NAME
FROM TCUSTOMER C
LEFT (JOIN TANIMAL A INNER JOIN TANIMALTYPE AT ON A.TYPE = AT.ID) ON A.ID = C.ANIMAL
LEFT (JOIN TCAR CAR INNER JOIN TCARTYPE CART ON CAR.TYPE = CARTT.ID) ON CAR.ID = C.CAR
LEFT JOIN TPROFESSION P ON P.ID = C.PROFESSION
How to do this or what do I need to search for? "oracle nested joins" did not work
Thanks in advance