Query Question

Hi,


I need to make a Query where I have one Table, the problem its this one:

Each record have a lot of fields, one fields its the record that identifies the student, each studen could have brothers, these other students are in the same table, the the table have 2 fields that identifies the family members, intFamilyNumber and intFamiliyPosition.

my problem its this one:


I need to make a query where my WHERE criteria applies to the records where the intFamilyPosition = 1, but also I need all records where of the same family (intFamilyNumber), the problem is, if the Big Brother not match with the WHERE criteria, then don't add his brothers either.

Thanks

i am confused!
show me the table and your SQL query.

Table:

Student Name | .. blah blah | Age | intFamilyNum | intFamilyPosition
Miguel Angel | | 22 | 123 | 1
Francisco | | 10 | 123 | 2
Daniel | | 19 | 456 | 1
Diana | | 7 | 456 | 2


Miguel Angel is the big brother (intFamilyPosition =1)
Francisco is my little brother (intFamilyPosition<>1)

Example:
I need to make a Query where the big brother have 22 years old.

I need all brother of the big brother in the result.

I mean, apply the WHERE (Filter) to brother only, but if the big brother not match with the criteria , Dont add to the brother to the result.

I hope make sense

Thanks !!

"SELECT * FROM Table WHERE Age =22"

intentá esto:

SELECT *
FROM Table as ONE
WHERE EXISTS (SELECT * FROM TABLE AS TWO
WHERE TWO.intFamilyPosition = 1
AND TWO.Age=22
AND ONE.intFamilyNum=TWO.intFamilyNum)

what means WHERE EXISTS ?

Muchas gracias Verito !

Verito:


I tried that and not works, don't returns any record :(

I have another solution:

If I could make a Query View where shows me the Student data and some big brother data in the same record, I can make a SELECT from there using a simple WHERE clause, but the hard thing its create the Query View.

Example using the previous Table :

Result Records:

Student Name | Age | intFamilyNum | intFamilyPosition | BigBrotherName
Miguel Angel | 22 | 123 | 1 | Miguel Angel
Francisco | 10 | 123 | 2 | Miguel Angel
Daniel | 19 | 456 | 1 | Daniel
Diana | 7 | 456 | 2 | Daniel



I hope make sense :D

Thanks

its possible to do this:

SELECT A.txAluNombre As StudentName,A.inFamLlave, B.txAluNombre AS BiBrotherName FROM catAlumnos AS A INNER JOIN (SELECT txAluNombre, inFamLlave FROM catAlumnos AS B WHERE B.inAluFamPosicion=1) ON A.inFamLlave = B.inFamLlave


I'm making a Inner JOIN from a Table and a SELECT statement.

its possible to make ???

Thanks