Using MySQL 5, MySQLQuery (latest). Complete Newbie,

I have the following query:

SELECT i.IndivId, i.Surname, i.First_Names, i.Parents,
(SELECT i.Surname FROM individuals i, families f
where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
as "father"
FROM individuals i
where i.Parents>0
order by i.Parents;

It returns nulls for the subselect. There are 2 tables, individuals (containing all persons including those who are parents) and Parents (with 2 fields mother & father, with the same format as i.indivId).

I am trying to use the father id. in the Parents table to cause the individual id. (being the father of the individual(s)) to be named in the father column.

I.parents in the 1st table points to the PriKey for the parents record in the Parents table. Then I am using one parent Id. in the Parents table to point back to the parents indivudual record to get their name.

So I end up with:
<Person Cname><person Sname><fatherName><motherName> in the output.

I have a feeling I'm going wrong here. What basic SQL will do this?

I hope the query above is pretty self explanatory!

this line looks a little funny...how can the individual_id also map to the family_id??? (and you have a few typos )
Anyways, that's not your problem...I think all you have to do is remove the reference to the `individuals` table from your nested query assuming all of your relationships are correct.

Thanks jFulton for that but it did not work and gave the same result. The individuals table looks like this:

IndivId (PriKey)
1stNames
Surname
Sex
BDate
.
.
.
Parents (same as Family_Id in Families table)

The Families table looks like this:

Family_Id (Pri Key) same as Parents in table above.
Father_Id
Mother_Id
.
.

I am trying to get an output which look like this:

IndivId, Surname, 1stNames, Father_id, Mother_Id

Where the last two point to IndivId in the individuals table (same type) so as to be able to extract the 1stname & surname of the individual parents.

I wonder if the columns 'parents' and 'family_Id' should have the same name?

Anyway I hope this helps further

Can you post an actual dump of the table structure and try to explain the relationships? It seems like this may be more of a design flaw than a bad query.

The names of the columns won't matter as long as the data in them is correct.


My next guess at a suitable query would be something like:
Make sure the nested select is getting the right values independently. Just replace i.Family_ID with values.

Thanks JFulton, that has worked with a minor tweak. I shall now go away and study this in my SQL book to make sure the lesson sticks. Until the next hurdle...

Many thanks.