query access question???

[dawilis]

My application is for breeding horses. I have a table GreyBeast with the animals information ,including the Mother as a number (or id to another Horse) same with the father, and other of-spring of that animal, when displaying the Horses information I can display the mother and father as a number (or ID ) quite easily.
My question is
How do I re query the database to get the mothers name based on the Id retrieved from the informatoin of the horse being qeried?

GreyBeast = Table Name
GreyID = Autonumber
GreyName = Horse name
GreySire1 = Mother = 5 Id of mother in same table
GreySire2 = Father = 20 Id of father in same Table
GreyProgidy1 = progidy 1 Id of offspring 1 in same table

Hope Ive explained this well enough

[raf]

you probably can use a subquery.

like

sql= "select Greyname from Greybeast where GreyID in (select GreyID from Greybeast where Greysire1 = 5)"


or, if you start from the offspring:


sql= "select Greyname from Greybeast where GreyID in (select GreyID from Greybeast where Greysire1 in (select Greysire1 from Greybeast where GreyProgidy1 =theprodigy))"

sql =replace(sql,"theprodigy",request.form("offspring"))


you see ? You just stich a selectquery in front of the query you now use, and use the Id as a condition.

(Querying hierarchical databases isn't always as easy as relational ones)

[whammy]

That should work, I use it a lot. You can also use NOT IN(criteria here) if you want to exclude records.

But there should be no spaces in the id, i.e. GreyID should be one word.

[raf]

whammy,

i've removed the spaces. thanks for noticing.

dawilis,

did this help ?

[dawilis]

Bear with me Im working through it

[david7777]

Let me just get this straight - You want the horse information to come up, and instead of the mother's ID coming up, you want the mothers name to come up?

Since you dont know how many offspring there might be, you will probably have to do something like this:

1. Get the horse details you are looking for including the mother and father's details
ie:

Select * from GreyBeast gb1, GreyBeast gb2, GreyBeast gb3
where gb1 .GreySire1 = gb2 .GreyID
and gb1 .GreySire2 = gb3.GreyID


2. Because you you dont know how many offspring there are, now you have to do a seperate query on them:

Select * from GreyBeast
where GreyID IN [offspringid's]

where [offspringid's] are all the offspring ids.

3. This will return all the info of the horse you are looking at, the mothers details, the fathers details and the offspring details seperately. You then use them how you want.

I havent tried this, but it seems logical.

Hope i helped

[raf]

you don't need a 'separate' query. Just use the first query as a subquery.
the RDBM will create a temporarely recordset collection, that you can use in the condition of another query.
(note : not all db-formats support this feature. In MySQL it's only available since version 4.1)

in a nicely normalized relational db, you wount offten need this soort a thing (though it will happen sometimes). If you have more a sort of hierarchical db, this will be the most frequently used querys


here's some more info from the MsAccess helpfile
-------------------------
SQL Subqueries
A subquery is a SELECT statement nested inside a SELECT, SELECT...INTO, INSERT...INTO, DELETE, or UPDATE statement or inside another subquery.

Syntax
You can use three forms of syntax to create a subquery:

comparison [ANY | ALL | SOME] (sqlstatement)

expression [NOT] IN (sqlstatement)

[NOT] EXISTS (sqlstatement)

A subquery has these parts:

Part Description
comparison An expression and a comparison operator that compares the expression with the results of the subquery.
expression An expression for which the result set of the subquery is searched.
sqlstatement A SELECT statement, following the same format and rules as any other SELECT statement. It must be enclosed in parentheses.


Remarks
You can use a subquery instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause. In a subquery, you use a SELECT statement to provide a set of one or more specific values to evaluate in the WHERE or HAVING clause expression.

Use the ANY or SOME predicate, which are synonymous, to retrieve records in the main query that satisfy the comparison with any records retrieved in the subquery. The following example returns all products whose unit price is greater than that of any product sold at a discount of 25 percent or more:

SELECT * FROM Products

WHERE UnitPrice > ANY

(SELECT UnitPrice FROM OrderDetails

WHERE Discount >= .25);


Use the ALL predicate to retrieve only those records in the main query that satisfy the comparison with all records retrieved in the subquery. If you changed ANY to ALL in the previous example, the query would return only those products whose unit price is greater than that of all products sold at a discount of 25 percent or more. This is much more restrictive.

Use the IN predicate to retrieve only those records in the main query for which some record in the subquery contains an equal value. The following example returns all products with a discount of 25 percent or more:

SELECT * FROM Products

WHERE ProductID IN

(SELECT ProductID FROM OrderDetails

WHERE Discount >= .25);


Conversely, you can use NOT IN to retrieve only those records in the main query for which no record in the subquery contains an equal value.

Use the EXISTS predicate (with the optional NOT reserved word) in true/false comparisons to determine whether the subquery returns any records.

You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees having the same job title. The Employees table is given the alias "T1":

SELECT LastName,

FirstName, Title, Salary

FROM Employees AS T1

WHERE Salary >=

(SELECT Avg(Salary)

FROM Employees

WHERE T1.Title = Employees.Title) Order by Title;


In the preceding example, the AS reserved word is optional.

Some subqueries are allowed in crosstab queries — specifically, as predicates (those in the WHERE clause). Subqueries as output (those in the SELECT list) are not allowed in crosstab queries.

[david7777]

That sounds more logical and realistic... I knew there must be a better way then mine, but ive never had to do it, so ive never taken the time to find out... Now i know!

Thanx raf :thumbsup: