Grouping records

Hi,

I have the following tables :

Town
towncode
townname

Area
areano
areaname
towncode

Locality

locno
areano

RequestType
reqid
reqdtls

Eg:
1 - Addition
2 - Removal
3 - Relocate

WebSummit

SummitId

RequestorName

DateOfRequest

reqid

Areano

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

Regards,

Vidya.

Vidya:

You have given a good description of your problem that includes (1) table schema (2) current query and (3) the specific problem -- duplicate rows. What is missing here is (1) sample data and (2) desired output. Without this we must to some extent guess at the problem. You might be able to fix the problem by simply adding a DISTINCT clause to your select statement. This can be done by changing the word SELECT into SELECT DISTINCT.

|||

IF I understand you correctly, you may want something more like this:

Code Snippet

SELECT DISTINCT
w.SummitId,
r.ReqDtLs,
a.AreaName,
l.LocNo,
t.TownName
FROM TownList t
JOIN Area a
ON t.TownCode = a.TownCode
JOIN Locality l
ON a.AreaNo = l.AreaNo
JOIN WebRequest w
ON w.AreaNo = a.AreaNo
JOIN RequestType ar
ON r.ReqID = w.ReqID
WHERE ( w.DateOfRequest >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND w.DateOfRequest < dateadd( month, datediff( month, 0, getdate() ), 0 )

Without sample data, we can't test our suggestions.

|||

Hi,

Yes I understand that without sample data it is difficult to test the query. I am sorry.

I will try and take care of this point the next time i need some help.

Thanks for the help anyways. I will test it at my end and revert back.

Regards,

Vidya.

|||

Hi,

The results are the same as I got

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

1 John 12/6/2007 1 1

2 Jack 13/6/2007 1 1

3 Bill 12/6/2007 2 2

4 Ben 12/6/2007 2 2

5 Dale 14/6/2007 2 3

6 Evjen 15/6/2007 3 1

7 Fuller 16/6/2007 1 4

8 Jimmy 16/6/2007 3 4

9 Kart 16/6/2007 1 5

10 Fuller 16/6/2007 1 5

Regards,

Vidya.

|||

Hello,

Any clues why the query is returning duplicate records. The data gets duplicated for every locality in that area.

Regards,

Vidya

|||

Thanks for providing the table information. It is now possible to realize that it is impossible to get the information you desire. There are significant 'design problems' that will prevent you from accurately getting what you want.

You are getting duplicates because each Area has Two Localities, there is NO way to associate a Town to a specific Locality, AND there is no way to associate a specific town to WebSummit Request.

Therefore, the result shows all Localities and all Towns for an Area.

(I think you want the specific town related to a WebSummit request.)

Here are my suggestions about 'design problems' that should be corrected.

The Towns table 'should' have LocNo. Locality is an attribute of the Town. From the LocNo, we can get the Area.

The Areas table 'should NOT' have the TownCode. TownName is NOT an attribute of the Area.

The WebSummit table 'should' have the TownCode -NOT the AreaNo. Without the TownCode, you cannot break it down below the level of the Area. You have NO way to associate a Locality or Town to a WebSummit row.

Think of it this way: A Ttown belongs to a Locality, a Locality belongs to an Area. A WebSummit Request is related to a specific Town, NOT to all towns in a Locality, or NOT to all towns in an Area.

(At least that is my interpretation of your outline -and it's late, so I could be totally wrong...)

|||

Hi Arnie,

You have written :

A Ttown belongs to a Locality, a Locality belongs to an Area.

whereas

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

A locality belongs to an area, an area belongs to a town.

Regards,

Vidya.

|||

Thanks for clarifying that. This is why this process works so much better when we are provided table DDL, sample data, desired results, AND an explanition of the data model.

Well, I was making assumptions based upon my interpretation of the data you provided. As I said, it's late, and I made some incorrect assumptions.

In your sample data, you have a Location (LocNo 1) belonging to multiple Areas. Is that correct?

INSERT INTO @.Locality VALUES ( 1, 1 )
INSERT INTO @.Locality VALUES ( 2, 1 )
INSERT INTO @.Locality VALUES ( 3, 1 )
INSERT INTO @.Locality VALUES ( 1, 2 )
INSERT INTO @.Locality VALUES ( 2, 2 )

And the WebSummit rows are related to Areas, not Locality. So any attempt to bring Locality into the resultset will entail having all Localities for an Area.

It still seems that the WebSummit data does not capture the finest granular information and most likely should have LocNo instead of AreaNo. Otherwise, by removing Locality from the resultset, you can get one row per row in the WebSummit table.

My apologies for the misinterpretation...

|||

Hi Arnie,

Its my fault again. I shortened the structure of the websummit table to explain my problem.

The websummit contains the localityno as well as the areano.

I also forgot to mention that in table locality, both the LocNo and AreaNo together form a primary key.

You can easily change the data to avoid confusion :

Locality

LocNo AreaNo

1 1

2 1

3 1

33 2

34 2

35 2

1 3

2 3

6 3

66 4

67 4

22 5

23 5

24 5

Now even though locality 1 may be in area 1 or area 3 as shown in teh data above, the uniqueness is achieved using both the locno and areano together.

SummitId RequestorName DateOfRequest reqid AreaNo LocNo

1 John 12/6/2007 1 1 1

2 Jack 13/6/2007 1 1 2

3 Bill 12/6/2007 2 2 34

4 Ben 12/6/2007 2 2 35

5 Dale 14/6/2007 2 3 6

6 Evjen 15/6/2007 3 1 3

7 Fuller 16/6/2007 1 4 66

8 Jimmy 16/6/2007 3 4 66

9 Kart 16/6/2007 1 5 23

10 Fuller 16/6/2007 1 5 24

So in short, a locality is just a no. given to divide areas, however its important. A locality belongs to only one area since both locno and area no together form a unique row.

If i have caused much confusion, I am ready to start over again with a fresh query, explaining everything in one go.

Regards,

Vidya.

|||

Vidya,

Thanks, that was the 'missing' piece. Now it becomes possible to link all the data. (Amazing what we can do when we have all the 'facts'.)

This 'should' work for you as you have explained the problem. Note that I changed the WebSummit dates so the the previous month filter will work properly. I added a few rows of data that should be out of range in order to test exculsion.

Code Snippet

SET NOCOUNT ON

DECLARE @.Towns table
( TownCode int,
TownName varchar(20)
)

INSERT INTO @.Towns VALUES ( 1, 'Conteck' )
INSERT INTO @.Towns VALUES ( 3, 'BigConteck' )

DECLARE @.Areas table
( AreaNo int,
AreaName varchar(20),
TownCode int
)

INSERT INTO @.Areas VALUES ( 1, 'Area1', 1 )
INSERT INTO @.Areas VALUES ( 2, 'Area2', 1 )
INSERT INTO @.Areas VALUES ( 3, 'Area3', 1 )
INSERT INTO @.Areas VALUES ( 4, 'Area4', 1 )
INSERT INTO @.Areas VALUES ( 5, 'Area5', 1 )
INSERT INTO @.Areas VALUES ( 6, 'Area6', 1 )
INSERT INTO @.Areas VALUES ( 7, 'Area7', 1 )
INSERT INTO @.Areas VALUES ( 8, 'Area1', 3 )

DECLARE @.Locality table
( LocNo int,
AreaNo int
)

INSERT INTO @.Locality VALUES ( 1, 1 )
INSERT INTO @.Locality VALUES ( 2, 1 )
INSERT INTO @.Locality VALUES ( 3, 1 )
INSERT INTO @.Locality VALUES ( 33, 2 )
INSERT INTO @.Locality VALUES ( 34, 2 )
INSERT INTO @.Locality VALUES ( 35, 2 )
INSERT INTO @.Locality VALUES ( 1, 3 )
INSERT INTO @.Locality VALUES ( 2, 3 )
INSERT INTO @.Locality VALUES ( 6, 3 )
INSERT INTO @.Locality VALUES ( 10, 3 )
INSERT INTO @.Locality VALUES ( 11, 3 )
INSERT INTO @.Locality VALUES ( 66, 4 )
INSERT INTO @.Locality VALUES ( 67, 4 )
INSERT INTO @.Locality VALUES ( 22, 5 )
INSERT INTO @.Locality VALUES ( 23, 5 )
INSERT INTO @.Locality VALUES ( 24, 5 )

DECLARE @.RequestType table
( ReqID int,
ReqdTLS varchar(20)
)

INSERT INTO @.RequestType VALUES ( 1, 'Addition' )
INSERT INTO @.RequestType VALUES ( 2, 'Removal' )
INSERT INTO @.RequestType VALUES ( 3, 'Relocate' )

DECLARE @.WebSummit table
( SummitId int,
RequestorName varchar(20),
DateOfRequest datetime,
ReqID int,
AreaNo int,
LocNo int
)

INSERT INTO @.WebSummit VALUES ( 1, 'John', '2007/05/12', 1, 1, 1 )
INSERT INTO @.WebSummit VALUES ( 2, 'Jack', '2007/05/13', 1, 1, 2 )
INSERT INTO @.WebSummit VALUES ( 3, 'Bill', '2007/05/12', 2, 2, 34 )
INSERT INTO @.WebSummit VALUES ( 4, 'Ben', '2007/05/12', 2, 2, 35 )
INSERT INTO @.WebSummit VALUES ( 5, 'Dale', '2007/05/14', 2, 3, 6 )
INSERT INTO @.WebSummit VALUES ( 6, 'Evjen', '2007/05/15', 3, 1, 3 )
INSERT INTO @.WebSummit VALUES ( 7, 'Fuller', '2007/05/16', 1, 4, 66 )
INSERT INTO @.WebSummit VALUES ( 8, 'Jimmy', '2007/05/16', 3, 4, 66 )
INSERT INTO @.WebSummit VALUES ( 9, 'Kart', '2007/05/16', 1, 5, 23 )
INSERT INTO @.WebSummit VALUES ( 10, 'Fuller', '2007/05/16', 1, 5, 24 )
INSERT INTO @.WebSummit VALUES ( 11, 'Kart', '2007/06/16', 1, 5, 23 )
INSERT INTO @.WebSummit VALUES ( 12, 'Fuller', '2007/06/16', 1, 5, 24 )

SELECT DISTINCT
w.SummitId,
r.ReqDtLs,
a.AreaName,
a.AreaNo,
l.LocNo,
t.TownName
FROM @.WebSummit w
JOIN @.Locality l
ON ( w.AreaNo = l.AreaNo
AND w.LocNo = l.LocNo
)
JOIN @.Areas a
ON w.AreaNo = a.AreaNo
JOIN @.Towns t
ON t.TownCode = a.TownCode
JOIN @.RequestType r
ON w.ReqID = r.ReqID
WHERE ( w.DateOfRequest >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND w.DateOfRequest < dateadd( month, datediff( month, 0, getdate() ), 0 )
)

|||

Bingo!!

Thanks a ton Arnie. Indeed, that's the query I was trying out.

Do me another favour. Suggest me a book to study T-sql or Sql server. I would now like to dedicate a lot of time to get strong in Sql.

See arnie, what you just did!! You inspired me!!

Regards,

Vidya.

|||

Itzak Ben-Gan has two books out on Microsoft Press -they are excellent!

A VERY worthwhile investment in your skills.

Good luck!