INNER JOIN - INSERT

[Clive Swan]

Hi,

My GIS software has a tool to count the number of points within a
grid.
This is fine for small recordset, when you get into the tens thousands
it
becomes unfriendly.

It must be possible (more efficent??) to do a select statement from
the two tables and insert the result into a column??

Table Property has thousands of records that fall within each record
of Table Ward.

Expect the SQL would be

SELECT [Property].BedRmNumber FROM [Ward].LA
WHERE [Property].LA = [Ward].LA

Surely this would need a loop.

Could anyone help???

Thanks

clive

[Erland Sommarskog]

Clive Swan (cl*******@yahoo.co.uk) writes:

My GIS software has a tool to count the number of points within a grid.
This is fine for small recordset, when you get into the tens thousands
it becomes unfriendly.

It must be possible (more efficent??) to do a select statement from
the two tables and insert the result into a column??

Table Property has thousands of records that fall within each record
of Table Ward.

Expect the SQL would be

SELECT [Property].BedRmNumber FROM [Ward].LA
WHERE [Property].LA = [Ward].LA

Surely this would need a loop.

Loops are rarely effective.

It is not very clear from your post what you want to do. You talk
about selecting a count, but the SELECT statement you have lists a
column.

Doing a very wild guess, this may be what you are looking for:

SELECT P.BedRmNumber, COUNT(*)
FROM Ward W
JOIN Property P ON W.LA = P.LA
GROUP BY P.BedRmNumber

The usual recommendation for these type of questions is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

The less you include of this, the more guesswork you will get in
response.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Clive Swan]

On 13 Aug, 13:26, Erland Sommarskog <[e-mail]:

Clive Swan ([e-mail]) writes:

My GIS software has a tool to count the number of points within a grid.
This is fine for small recordset, when you get into the tens thousands
it becomes unfriendly.

It must be possible (more efficent??) to do a select statement from
the two tables and insert the result into a column??

Table Property has thousands of records that fall within each record
of Table Ward.

Expect the SQL would be

SELECT [Property].BedRmNumber FROM [Ward].LA
WHERE [Property].LA = [Ward].LA

Surely this would need a loop.

Loops are rarely effective.

It is not very clear from your post what you want to do. You talk
about selecting a count, but the SELECT statement you have lists a
column.

Doing a very wild guess, this may be what you are looking for:

SELECT P.BedRmNumber, COUNT(*)
FROM Ward W
JOIN Property P ON W.LA = P.LA
GROUP BY P.BedRmNumber

The usual recommendation for these type of questions is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

The less you include of this, the more guesswork you will get in
response.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -

Hi,

Hope this is clearer.

I have a one-to-many relationship between [Ward].LA
and [Property].BedroomNumber.

For example
[Property].BedroomNumber [Property].LA
1 00AA
5 00AA
10 00AA
15 00AA
20 00AA
10 00AA
25 00AA

1 00AB
1 00AB
2 00AB
1 00AB
20 00AB
10 00AB
25 00AB

[Ward].LA
00AA
00AB
00AC
00AD
00AE
00AF

[Ward] may have 10,000 records while [Property] might have
1 million records.

I want to count and add up all the [Property].BedroomNumber with
a unique [Property].LA, then add the result set to
[Ward].BedroomNumber.

So that I would have the following result:

[Ward].LA [Ward].BedroomNumber
00AA 78
00AB 60
00AC 10
00AD 100
00AE 150
00AF 20

[Erland Sommarskog]

Clive Swan (cl*******@yahoo.co.uk) writes:

I have a one-to-many relationship between [Ward].LA
and [Property].BedroomNumber.

For example
[Property].BedroomNumber [Property].LA
1 00AA
5 00AA
10 00AA
15 00AA
20 00AA
10 00AA
25 00AA

1 00AB
1 00AB
2 00AB
1 00AB
20 00AB
10 00AB
25 00AB

[Ward].LA
00AA
00AB
00AC
00AD
00AE
00AF

[Ward] may have 10,000 records while [Property] might have
1 million records.

I want to count and add up all the [Property].BedroomNumber with
a unique [Property].LA, then add the result set to
[Ward].BedroomNumber.

So that I would have the following result:

[Ward].LA [Ward].BedroomNumber
00AA 78
00AB 60
00AC 10
00AD 100
00AE 150
00AF 20

Maybe:

UPDATE Ward
SET BedroomNumber = P.cnt
FROM Ward W
JOIN (SELECT LA, COUNT(*) AS cnt
FROM Property
GROUP BY LA) P ON W.LA = P.LA

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Clive Swan]

On 13 Aug, 22:58, Erland Sommarskog <[e-mail]:

Clive Swan ([e-mail]) writes:

I have a one-to-many relationship between [Ward].LA
and [Property].BedroomNumber.

For example
[Property].BedroomNumber [Property].LA
1 00AA
5 00AA
10 00AA
15 00AA
20 00AA
10 00AA
25 00AA

1 00AB
1 00AB
2 00AB
1 00AB
20 00AB
10 00AB
25 00AB

[Ward].LA
00AA
00AB
00AC
00AD
00AE
00AF

[Ward] may have 10,000 records while [Property] might have
1 million records.

I want to count and add up all the [Property].BedroomNumber with
a unique [Property].LA, then add the result set to
[Ward].BedroomNumber.

So that I would have the following result:

[Ward].LA [Ward].BedroomNumber
00AA 78
00AB 60
00AC 10
00AD 100
00AE 150
00AF 20

Maybe:

UPDATE Ward
SET BedroomNumber = P.cnt
FROM Ward W
JOIN (SELECT LA, COUNT(*) AS cnt
FROM Property
GROUP BY LA) P ON W.LA = P.LA

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -

Thanks,

Will give that a try.

Clive

[Ed Murphy]

Erland Sommarskog wrote:

Clive Swan (cl*******@yahoo.co.uk) writes:

>I have a one-to-many relationship between [Ward].LA
and [Property].BedroomNumber.

For example
[Property].BedroomNumber [Property].LA
1 00AA
5 00AA
10 00AA
15 00AA
20 00AA
10 00AA
25 00AA

1 00AB
1 00AB
2 00AB
1 00AB
20 00AB
10 00AB
25 00AB

[Ward].LA
00AA
00AB
00AC
00AD
00AE
00AF

[Ward] may have 10,000 records while [Property] might have
1 million records.

I want to count and add up all the [Property].BedroomNumber with
a unique [Property].LA, then add the result set to
[Ward].BedroomNumber.

So that I would have the following result:

[Ward].LA [Ward].BedroomNumber
00AA 78
00AB 60
00AC 10
00AD 100
00AE 150
00AF 20

Maybe:

UPDATE Ward
SET BedroomNumber = P.cnt
FROM Ward W
JOIN (SELECT LA, COUNT(*) AS cnt
FROM Property
GROUP BY LA) P ON W.LA = P.LA

SUM() rather than COUNT(), surely? Look again at his desired
results. (Okay, so 78 is too low, but 60 is spot on.)