Sql Problem,

[nilin]

Hello,
Can anybody guide me , what should be the sql for following criteria,
Emp_master table has columns (empid,empname,deptid,joindt)
Dept_master table has columns (deptid,deptname)
Now I want to find out how many emp. are there in each Dept?

Can u give me the Sql?
nilin

 

[PHV]

Try something like this;[tt]
SELECT D.deptid,D.deptname,COUNT(*) count_emp
FROM Emp_master E,Dept_master D
WHERE E.deptid=D.deptid
GROUP BY 1,2[/tt]

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[nilin]

What would be the diff. between your sql and following one?

SELECT D.deptid,D.deptname,COUNT(E.deptid) count_emp
FROM Emp_master E,Dept_master D
WHERE E.deptid=D.deptid
GROUP BY 1,2


thaning you,
nilin

 

[nilin]

What is the meaning of following SQL?

SQL : Select * from employee where 1 = 0

 

[PHV]

Select * from employee where 1 = 0
This select returns no row but the structure of the employee table.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[nilin]

Hello,
Recently I am facing a problem as stated below, please help me out.
See i have two diff. tables in my database. and on a form i have array to display records from both of these two tables.
now when a form loads i want select records from both of these table and display them. I write two diff. functions, function f1() - to select records from table1
function f2() - to select records from table2 (and appends to program variable array m_array1)
I am calling both these functions one after other and then display my program variable array m_array1.


Now if f1 function FETCH failed (record not found) it gives error for "PREPARE" statement in function f2 saying "prepare failed - sqlcode = 100" .

there can be a situation where table1 does not have any matching rows, and table2 has or vice verssa , or both tables has the matching rows , or both may not.

Hope I explained the problem clearly , if not please let me know i will explain in more detail. please help me ,

Thanking you,
Nilin

 

[PHV]

Why not use a UNION query ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884