Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 26th, 2004, 05:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query Problem group by

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
Reply With Quote
  #2 (permalink)  
Old January 27th, 2004, 04:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old January 29th, 2004, 04:41 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Reply With Quote
  #4 (permalink)  
Old January 30th, 2004, 06:41 AM
Authorized User
 
Join Date: Jan 2004
Location: Pune, MAHARASHTRA, India.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to ankur_vachhani
Default

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
Reply With Quote
  #5 (permalink)  
Old February 4th, 2004, 02:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Quote:
quote:Originally posted by bmains
 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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
One Record From Each Group - Query rstelma SQL Server 2000 7 January 3rd, 2008 12:08 AM
SQL query using "Group By" - please help BananaJim SQL Language 2 February 26th, 2007 10:23 AM
SQL query retrieving last record and group by snowy SQL Language 2 December 13th, 2006 01:59 PM
Group by Weeks in a query or table lryckman Access VBA 4 December 4th, 2006 08:55 AM
Group By query with count problem ptaylor SQL Language 2 May 12th, 2004 09:27 AM



All times are GMT -4. The time now is 11:15 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.