我不知道如何更好地解释:
我需要一个视图(以非常规范的方式),将ID扩展为相应的名称,以便我需要以以下方式组合左连接和内连接(请参见示例)
表格:
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
我需要一个视图,结果如下:
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|
我想我需要类似于这样的东西
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
如何操作或者我需要搜索什么?"oracle nested joins"没有用
提前感谢