Unanswered: Trying to get a column from another table.
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))
FROM individuals i
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!
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.
I wonder if the columns 'parents' and 'family_Id' should have the same name?
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:
, families f
(i2.IndivId = f.father_ID)
(i.Family_ID = f.Family_ID)
) as Father
Make sure the nested select is getting the right values independently. Just replace i.Family_ID with values.