如何在单个视图中连接两个表 [英] How to join two table in a single View
问题描述
我有两个表格 EMP_DETAILS ,其中包含7列( EMP_ID
[主键] ,FIRST_NAME,LAST_NAME ,年龄,地址,薪水,DEPT_ID
[外键] )另一个是 DEP_DETAILS ,其中包含2列( DEPT_ID [主键] ,DEPT_NAME )。
我想创建一个 View 对于那些表但没有使用 JOIN查询。意思是:
EMP_ID FIRST_NAME LAST_NAME年龄地址SALED DEPT_ID DEPT_NAME
I have two table one is EMP_DETAILS which is containing 7 columns (EMP_ID
[Primary Key], FIRST_NAME, LAST_NAME, AGE, ADDRESS, SALARY, DEPT_ID
[Foreign Key]) and another one is DEP_DETAILS which is containing 2 columns (DEPT_ID [Primary Key], DEPT_NAME).
I want to create a single View for those tables but without using JOIN Query. Means :
EMP_ID FIRST_NAME LAST_NAME AGE ADDRESS SALARY DEPT_ID DEPT_NAME
推荐答案
我想为这些表创建一个View但不使用JOIN
不幸的是,你不能!
我建议回归基础并阅读: SQL连接的可视化表示 [ ^ ]
可以使用where子句来完成。
像以前一样用来做这个,我相信在MS SQL Server 2000出来之前。
It can be done with a where clause.
Used to do it like this years ago, I believe before MS SQL Server 2000 come out.
select
FIRST_NAME,
LAST_NAME,
AGE,
ADDRESS,
SALARY,
DEPT_NAME.DEPT_ID,
DEPT_NAME
from EMP_DETAILS, DEPT_NAME
where EMP_DETAILS.DEPT_ID = DEPT_NAME.DEPT_ID
但我建议使用加入。
But I recommend to use join.
select
FIRST_NAME,
LAST_NAME,
AGE,
ADDRESS,
SALARY,
DEPT_NAME.DEPT_ID,
DEPT_NAME
from EMP_DETAILS
inner join DEPT_NAME
on EMP_DETAILS.DEPT_ID = DEPT_NAME.DEPT_ID
试试这个
Try this
select t1.EMP_ID, t1.FIRST_NAME,t1.LAST_NAME, t1.AGE, t1.ADDRESS, t1.SALARY, t2.DEPT_ID, t2.DEPT_NAME from EMP_DETAILS t1, DEP_DETAILS t2
这篇关于如何在单个视图中连接两个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!