I am developing Staff Allocation System,
database is sql server 2000.
I have problem in retrieve the staff informations,

Which employee working which Project and what project have assign to him, what is his assign project or contract no,

One employee working more then one project, retrieve information one employee how many projects are working,
What is his approved position, what is his assign position.
It the main data have to retrieve, as well as retrieve all fields which related to those tables.

I use this query.

select name,apppos approved_position,appcont approved_contract,appdate employee_appr_date,munref Municipality_Ref,dcilref DCIL_REF,projtype Project_Type,strdate Project_str_date,comdate Projcet_comp_date,extdate Proejct_ext_date,dept,emptype Employee_Type from contract,emp,apprecords where contract.rec_id=emp.rec_id and emp.rec_id=apprecords.rec_id and apprecords.name='dewachi'


above query retrieve no data,

how can use group by clause in the above query ?

group by apprecords.appcontract

group by clause give error.

above query have to retrieve data from the three tables, I have four tables, what query I use so that all four tables data retrieve like this.


Name, approved_position, approved_contract,assign_position,assign_contract, startdate,completion_date,,,,,,,,, and so on…
Group by apprecords.appposition
……….


Contract Table (basic data entry contract table)
---------------------------------------
rec_id
Contract No.
ProjectType
StartDate
CompletionDate
ExtendedDate


Employee Table (basic data entry employee table)
----------------------------------
rec_id
EmpNo
Name
Position
Department
EmployeeType


Approved Records Table (in this table all information about
                             the employee and his approved
                                position and contract )
----------------------------------------------
rec_id
Name
Approved Date
MunicipalityRefNo
DCILRefNo
ApprovedPosition
ApprovedContract


Assign Project Table (in this table all information about the
                         employee his assign the project)
-------------------------------------------------------------
rec_id
Name
AssignPosition
AssignContract
EmpProjectStartDate
EmpProjectEndDate
ShiftNo
ProjectStatus


Please help.
how can retrieve the data by four tables using group by clase ?

Regards.

Mateen

Using group by/having clause, the group by must contain all fields you are selecting other than aggregates. Typically you use the group by with aggregate functions. I hope this helps.

thanks for your response.
I use group by clause with having.
query return the data.


select e.empno,e.fname+' '+mname+''+lname,e.posit,contractno,projtype,extda te,dept,bstatus,appstrdate,munref,dcilref,astrdate ,aenddate,cshift,pstatus,a.apppos,aposit,appcont,a ppcont,strdate,comdate
from employees e
inner join approvals a on a.empno=e.empno and a.empno=e.empno
inner join contracts c on c.rec_id=e.rec_id
inner join assignments ap on ap.empno=e.empno
group by e.empno,e.fname+' '+mname+''+lname,e.posit,contractno,projtype,extda te,dept,bstatus,appstrdate,munref,dcilref,astrdate ,aenddate,cshift,pstatus,a.apppos,aposit,appcont,a ppcont,strdate,comdate
having appcont='2016'


regards.

Mateen

U can also user sub-query which will make query more readble and effcient

for example,

SQL> select name from first_table where proj in ( select proj from second_table);


            Hope this may help

thanks for your response.

I use following query
this query retrieve all data,
I want to separate the data to use group by clause
in contractno field.
it should retrieve all contractno and
related information from the four tables.


select e.empno,e.fname+' '+mname+''+lname fullname,e.posit,contractno,projtype,contname,cont amt,contr,jobno,strdate,comdate,extdate,dept,bstat us,appsta,aposit,appcont,appstrdate,munref,munletd ate,dcilref,dcilletdate,astrdate,aenddate,cshift,p status,a.apppos,alocat
from employees e
inner join approvals a on a.empno=e.empno and a.empno=e.empno
inner join contracts c on c.rec_id=e.rec_id
inner join assignments ap on ap.empno=e.empno
group by e.empno,e.fname+' '+mname+''+lname, e.posit,contractno,projtype,contname,contamt,contr ,jobno,strdate,comdate,extdate,dept,bstatus,appsta ,aposit,appcont,appstrdate,munref,munletdate,dcilr ef,dcilletdate,astrdate,aenddate,cshift,pstatus,a. apppos,alocat



I want to retrieve the data like

contractno 2016
data ..................

contractno 2015
data.................

contractno 2065
data.................

how can use above query so that data retreive
in group by cluase ?
separate contractno display and data retreive
from the four tables ?

regards.

Mateen