RE: SQL help
Try this:
Select stname, street, count(*)
>From h8_address, land_records
Where stname = street
Group by stname, street
Order by 1 desc, 2;
Rob
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]
On Behalf Of Mike Badar
Sent: Monday, June 26, 2006 3:33 PM
To: informix-list@iiug.org
Subject: 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
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list