INNER JOIN - INSERT

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

Re: INNER JOIN - INSERT

Clive Swan (cliveswan@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: INNER JOIN - INSERT

On 13 Aug, 13:26, Erland Sommarskog wrote:
> Clive Swan (clives...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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

Re: INNER JOIN - INSERT

Perhaps this will get you started.

SELECT [Ward].LA,
COUNT([Ward].BedroomNumber) as Rooms
FROM [Ward]
GROUP BY [Ward].LA
ORDER BY [Ward].LA

Roy Harvey
Beacon Falls, CT

On Mon, 13 Aug 2007 06:26:45 -0700, Clive Swan
wrote:

>On 13 Aug, 13:26, Erland Sommarskog wrote:
>> Clive Swan (clives...@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, esq...@sommarskog.se
>>
>> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
>> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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
>
>

Re: INNER JOIN - INSERT

Clive Swan (cliveswan@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: INNER JOIN - INSERT

On 13 Aug, 22:58, Erland Sommarskog wrote:
> Clive Swan (clives...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx- Hide quoted text -
>
> - Show quoted text -

Thanks,

Will give that a try.

Clive

Re: INNER JOIN - INSERT

Erland Sommarskog wrote:

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