Oracle- SQL: combine LEFT and INNER JOIN

Viewed 982

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

1 Answers
Related