a simple sql problem

Hi friends,

I am stuck in solving the sql query.PI am not good at sql query,Please give me the solution ion this.

In my table, there are 3 fields say MainId, Name and parentId.
MainId is auto increment field and ParentId store the value of MainId.
a) Now I want to find the name and their parentId
b) Count of ParentId for the mainId.

How do I get in sql query.

Thsnks & Regsrds

PPCC

This code should do the trick, you need to replace MAINID with the value you are looking for...
SELECT COUNT(ParentID) FROM [TABLE] WHERE ParentID = MAINID

I'm not sure to understand. I suppose you want to choose a name, perhaps from a list, and find all records with same parent id. Probably you could use nested query, but I don't remember well the sintax :bigyello:
So I suggest to find, before, the parentid from Name:

"SELECT parentid FROM MyTable WHERE Name='" & StrName & "'"

StrName is a string which contains the name you selected


Now you have parentid and you can assign its value to a variable (AS LONG) that I call IDP
"SELECT * FROM MyTable WHERE ParentID=" & IDP.Tostring
In this way you select all records with ParentID=IDP


If you want their number, for a particular IDP:
"SELECT COUNT(*) AS Total FROM MyTable WHERE ParentID=" & IDP.Tostring
In 'Total' you obtain the sum of all records with ParentID=IDP

I'm using only my memory, because I've not your database to test my queries, so it's possible they aren't right. I can't be sure about this point. Sorry!