Grouping records based on more than one column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Grouping records based on more than one column

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-06-15 : 01:36:43

Hi,
I have the following tables :

Town
towncode
townname

Area
areano
areaname
towncode

Locality

locno(pk)
areano(pk)

RequestType
reqid
reqdtls

Eg:
1 - Addition
2 - Removal
3 - Relocate

WebSummit

SummitId
RequestorName
DateOfRequest
reqid
Areano
LocNo

A Town has areas, Areas have locality so...

I want to find out the Town/Area/Locality wise Addition/Removal/Relocation request that have come
during the last 1 month.

The query I have written so far is as follows :

SELECT WebRequest.SummitId, RequestType.reqdtls, Area.areaname, Locality.locno, Town.townname
FROM RequestType INNER JOIN
WebRequest ON RequestType.reqid = WebRequest.reqid INNER JOIN
Area ON WebRequest.areano = Area.areano INNER JOIN
TownList ON Area.towncode = TownList.towncode INNER JOIN
Locality ON Area.areano = Locality.areano
However the results are entirely incorrect with a lot of duplicates.

Kindly suggest me the right query

Here's some sample data

Town

TownCode TownName

1 Conteck

Area

AreaNo AreaName TownCode

1 Area1 1

2 Area2 1

3 Area 3 1

4 Area4 1

5 Area5 1

6 Area6 1

7 Area7 1

Locality

LocNo AreaNo

1 1

2 1

3 1

1 2

2 2

1 3

2 3

3 3

4 3

1 4

2 4

1 5

2 5

3 5

RequestType

reqid reqdtls

1 Addition

2 Removal

3 Relocate

WebSummit

SummitId RequestorName DateOfRequest reqid
AreaNo LocNo

1 John 12/6/2007 1 1 1

2 Jack 13/6/2007 1 1 1

3 Bill 12/6/2007 2 2 1

4 Ben 12/6/2007 2 2 2

5 Dale 14/6/2007 2 3 2

6 Evjen 15/6/2007 3 1 3

7 Fuller 16/6/2007 1 4 1

8 Jimmy 16/6/2007 3 4 2

9 Kart 16/6/2007 1 5 2

10 Fuller 16/6/2007 1 5 3

Thanks,
Vids

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-15 : 08:10:08

try something like this, I don't know how to get the localtime at the moment

SELECT t.TownCode, a.AreaNo, w.DateOfRequest
FROM Town as t, Area as a, WebSummit as w
WHERE t.TownCode = a.TownCode
AND a.AreaNo = w.AreaNo
AND datediff(m, localtime, time month ago) < 1

lols
Posting Yak Master

174 Posts

Posted - 2007-06-15 : 08:28:44

Thanks Nowy,

I need the locality also in the query.

It has to be Town/Area/Locality wise Addition/Removal/Relocation request.

I am not too worried about the date. I have figured that out.

However in my query, whenever I add the locality, the data gets duplicated. The same happened in my query too.

Please suggest.

Thanks.

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-15 : 08:35:21

Maybe you can use "SELECT DISTINCT"?

lols
Posting Yak Master

174 Posts

Posted - 2007-06-15 : 14:25:45

I had tried adding the DISTINCT clause. It doesnt work.

However I think i got the answer on a different forum.

So thanks anyways.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources