Help with SQL Select statement

[chpicker]

Well, I'm still not getting it. Here's an example of what I want to accomplish.

I have 2 tables. The second has many records for each record in the first, and both are indexed on the controlling field. How would I make a Select statement that would get me one record for each record in the first table and a COUNT of the number of matching records in the second table? Say the first table has 1500 records and the second has 60000 records. The query results should give me 1500 records, each with a count of the number of matching records from the second table.

Every way I've tried has only yielded one record with a count of around 58000. Any ideas how to do this?

 

[fluteplr]

what is the join between the two tables? Provide some information about the table structures and we will provide some help.

 

[marsh]

If table a (one) and table b ( to many) are to be joined on field xyz (which is in both tables), try this:

select a.*, count(b.xyZ) from a join b on a.xyz = b.xyz group by b.xyz

Obviously, optimizing the query to fit your needs.

Hope this helps

 

[ramani]

SELECT a.*, COUNT(b.KeyField) AS myCount ;
FROM file1 a, file2 b ;
WHERE a.KeyField = b.KeyField GROUP BY a.keyfield

This shall give you the result.

ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[rajeevnandanmishra]

select a.keyfield[,a.column(s)], count(b.keyfield) from table1 a join table2 b on a.keyfield=b.keyfield group by a.keyfield[,a.column(s)]

This will definetly solve your problem!

 

[ronix]

I'm trying to delete some numbers from a memo field in Fox. The problem is that the number I want to delete are not found in the same place in the memo filed. What's a good way to accomplish this?

 

[chpicker]

Well, if it's a specific number you need to locate in the string, then you can try this. Assume that "memofld" is the name of the memo field in your table and your record pointer is on the correct record. To find where in the memo field the number 136 is, do this:

lnPosition=at("136",memofld)

Or, to find where the contents of a variable are found:

lnPosition=at(alltrim(str(nNumToFind)),memofld)

lnPosition will now contain the character position that the number appears at in the field. You can then use substr() or stuff() to replace the number with something else.