469,898 Members | 1,587 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

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

Aug 13 '07 #1
5 11790
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
Aug 13 '07 #2
On 13 Aug, 13:26, Erland Sommarskog <esq...@sommarskog.sewrote:
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/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

Aug 13 '07 #3
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
Aug 13 '07 #4
On 13 Aug, 22:58, Erland Sommarskog <esq...@sommarskog.sewrote:
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/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

Aug 15 '07 #5
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.)
Sep 10 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ike | last post: by
4 posts views Thread by Steve Hall | last post: by
3 posts views Thread by Prem | last post: by
8 posts views Thread by kieran | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.