Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Views containing Multiple Table queries??
Hi,
This is one of the main benefits of a view.
Here's a sample using DEPT and EMP tables..just adapt it to
your tables. You'll need an alias for the count( ) column...
create view deptemp as
select d.deptno,d.dname,count(e.empno) EMPLOYEES
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno,d.dname
SQL> SELECT * FROM DEPTEMP
2 ;
DEPTNO DNAME EMPLOYEES
---------- -------------- ----------
10 ACCOUNTING 11 20 RESEARCH 22 30 SALES 31 60 FINANCE 13 70 PLANNING 18 80 PURCHASING 17
6 rows selected.
Adrian Quezada wrote:
> HI All,
>
> Is it possible to build a view that queries multiple tables, but still
> be able to access the view as if it was single table?
>
> What I would to accomplish is a way that I can query my accounts table
> and then use the emp_id to determine how many enteries that emp_id has
> in another table. I am currently using multiple SQL statements:
>
> For the Accounts table:
> Select * from Accounts
>
> To find the number of instances
> select count(emp_id) from purchase where empid=(emp_id to look for);
>
> It would be great if I could something like:
>
> select * from EMPLOYEE_VIEW
>
> and have it return:
>
> Emp_id Name Count
> 11111 abc 20
> 22222 def 1
> 33333 ghi 20
>
> Is this even possible? If not, what would be the best way to
> accomplish this with a single select statement?
>
> Any and all help would be greatly appreciated.
Received on Mon Mar 26 2001 - 13:37:20 CST
![]() |
![]() |