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]

Re: INNER JOIN - INSERT

Clive Swan (cliveswan@yaho o.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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Clive Swan]

Re: INNER JOIN - INSERT

On 13 Aug, 13:26, Erland Sommarskog <esq...@sommars kog.sewrote:

Clive Swan (clives...@yaho o.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, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/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]

Re: INNER JOIN - INSERT

Clive Swan (cliveswan@yaho o.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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Clive Swan]

Re: INNER JOIN - INSERT

On 13 Aug, 22:58, Erland Sommarskog <esq...@sommars kog.sewrote:

Clive Swan (clives...@yaho o.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, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx- Hide quoted text -
>
- Show quoted text -

Thanks,

Will give that a try.

Clive

[Ed Murphy]

Re: INNER JOIN - INSERT

Erland Sommarskog wrote:

Clive Swan (cliveswan@yaho o.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.)