Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    7

    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))
    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!

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    this line looks a little funny...how can the individual_id also map to the family_id??? (and you have a few typos )
    Code:
    where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
    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.

  3. #3
    Join Date
    Feb 2006
    Posts
    7
    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

  4. #4
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    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:
    Code:
    SELECT 
         i.IndivId
         , i.Surname
         , i.First_Names
         , i.Parents,
         (SELECT 
              i2.Surname 
         FROM 
              individuals i2
              , families f
         WHERE 
              (i2.IndivId = f.father_ID) 
              AND 
              (i.Family_ID = f.Family_ID)
         ) as Father
    FROM 
         individuals i
    WHERE
    ...
    Make sure the nested select is getting the right values independently. Just replace i.Family_ID with values.

  5. #5
    Join Date
    Feb 2006
    Posts
    7

    This works

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •