SQL help
Greetings, I'm looking for some SQL help. I have two tables: Table 'h8_address' has approximately 370,000 rows; Table 'land_records' has approximately 5,400 rows; Both tables have an address column: Address column for table 'h8_address': stname Address column for table 'land_records': street Description of what I would like to accomplish: Find the number of rows from 'h8_address.stname' that match land_records.street. The count should be <= to the number of rows in the 'h8_address' table. A simple diagram may help clarify things: h8_address land_records ---------- ------------ stname street a a a b a c a d e f j q m r n n t z Based on the above diagram, the result I'm looking for would be: 5 rows; 4 rows: stname(a) = street(a); 1 row: stname(n) = street(n) Note: There is no PK/FK relationship between the two tables. Any help you can provide would be greatly appreciated. Mike Badar ESRI-Denver 1 International Court Broomfield, CO 80021-3200 303-449-7779 mbadar@esri.com www.esri.com
It seems like a simple:
select
count(*)
from
h8_address h,
land_records l
where
h.stname = l.street
;
--- TEST CASE
create table h8_address(
stname char(1)
) ;
create table land_records(
street char(1)
) ;
-- > h8_address
-- > ----------
-- > stname
insert into h8_address values ("a");
insert into h8_address values ("a");
insert into h8_address values ("a");
insert into h8_address values ("a");
insert into h8_address values ("e");
insert into h8_address values ("j");
insert into h8_address values ("m");
insert into h8_address values ("n");
insert into h8_address values ("t");
-- land_records
------------
-- > street
insert into land_records values ("a");
insert into land_records values ("b");
insert into land_records values ("c");
insert into land_records values ("d");
insert into land_records values ("f");
insert into land_records values ("q");
insert into land_records values ("r");
insert into land_records values ("n");
insert into land_records values ("z");
select
count(*)
from
h8_address h,
land_records l
where
h.stname = l.street
;
(count(*))
5
Of course Rob's query gives you a breakdown of the matches:
select
stname,
street,
count(*)
from
h8_address h,
land_records l
where
h.stname = l.street
group by
stname,
street
order by
1 desc, 2
;
stname street (count(*))
n n 1
a a 4
I am not sure why he is sorting by the stname field descending.
Mike Badar wrote:
> Greetings,
>
> I'm looking for some SQL help.
>
> I have two tables:
>
> Table 'h8_address' has approximately 370,000 rows;
> Table 'land_records' has approximately 5,400 rows;
>
> Both tables have an address column:
>
> Address column for table 'h8_address': stname
> Address column for table 'land_records': street
>
> Description of what I would like to accomplish:
>
> Find the number of rows from 'h8_address.stname' that match
> land_records.street. The count should be <= to the number of rows in
> the 'h8_address' table.
>
> A simple diagram may help clarify things:
>
> h8_address land_records
> ---------- ------------
>
> stname street
> a a
> a b
> a c
> a d
> e f
> j q
> m r
> n n
> t z
>
> Based on the above diagram, the result I'm looking for would be: 5 rows;
> 4 rows: stname(a) = street(a); 1 row: stname(n) = street(n)
>
> Note: There is no PK/FK relationship between the two tables.
>
> Any help you can provide would be greatly appreciated.
>
> Mike Badar
> ESRI-Denver
> 1 International Court
> Broomfield, CO 80021-3200
> 303-449-7779
> mbadar@esri.com
> www.esri.com