RE: SQL help
Thank you Rob and Bozon; their examples worked perfectly.
>From Rob:
Select stname, street, count(*)
>From h8_address, land_records
Where stname = street
Group by stname, street
Order by 1 desc, 2;
>From Bozon:
select
count(*)
from
h8_address h,
land_records l
where
h.stname = l.street
;
Mike
> -----Original Message-----
> From: informix-list-bounces@iiug.org
> [mailto:informix-list-bounces@iiug.org] On Behalf Of Mike Badar
> Sent: Monday, June 26, 2006 1: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
>
>